Working with nothing (not null) 2

[LaurenM]

Hey All, I'm working on report for the company website reporting various stats, what reports where run, where people when, etc. This report, the user selects a month and enters a year, and the various stats for the month selected and the month previous are populated into the report. Fairly basic overall, however the one thing that I am trying to be fancy on is this: these stats started being recorded in the middle of May of this year, I am wondering for example the user selects the data for May then there is no data for April, and the SQL returns nothing not NULL data, but nothing, if there is any way to handle that in the SQL, a similar function to nvl, except instead of NULL, it handles nothing. Once again if I am unclear about anything let me know.

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[SantaMufasa]

What we have here is a failure to communicate.

Lauren, In the Oracle World, we represent the absence of value by NULL. NULL is not a special ASCII value in Oracle...NULL is the situation where no bits even exist for a specific data item.


So, in your case, where no data exist for April, then I'm presuming that no record exists for April, right? In that case, we usually use a database feature called a LEFT OUTER JOIN. In such a case, rows from some base table exists, but rows (e.g., "statistics" in your case) do not exist. We then use a LEFT OUTER JOIN in Oracle to create "phantom" statistics records for each case where we would like rows (populated with NULL in each column) that act as a "match" to each of the base-table rows that otherwise would not have a match from the "statistics" table in your case.

You can then use an NULL comparison (or NVL function) against any of the values from the "phantom" row.

Is that what you are looking for? If so, please post the query that you have composed so far, then we can show you the two different styles of LEFT OUTER JOIN syntax available to you from Oracle.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[LaurenM]

There is only the one table, the first column is a date time stamp, and then each of the following columns have a value or a NULL, and then for each month I either sum the values in each column grouped by month, or in some cases I just grab the value on the last day of the month b/c the values are a cumlative total (don't ask me why it is like that, I just work here ).

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[SantaMufasa]

There is only the one table...

Then, Lauren, it makes life straightforward (and simple) for you, IMHO: You have full use of the "WHERE...IS NULL" (or "WHERE...IS NOT NULL") syntax, along with "NVL()" function use.

Perhaps I am just being dim (which is highly likely), but I don't follow your (perhaps perceived) limitation(s).

Are you able to post your existing code, some of its output, and how you would like the output to be different?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[LaurenM]

SELECT
     A.CURR_MONTH - B.LAST_MONTH AS PSPCOUNT 
FROM
     (SELECT 
          PSP_HITS AS CURR_MONTH
     FROM 
          V3W_STATS_ARCH_HEAD
     WHERE 
          TRUNC(ARCH_TIMESTAMP) = LAST_DAY(TO_DATE('01/05/2007', 'DD/MM/YYYY'))
          AND PLAN_TYPE = 'SHPP'
     ) A,
     (SELECT 
          PSP_HITS AS LAST_MONTH
     FROM 
          V3W_STATS_ARCH_HEAD
     WHERE 
          TRUNC(ARCH_TIMESTAMP) = LAST_DAY(TO_DATE('01/04/2007', 'DD/MM/YYYY'))
          AND PLAN_TYPE = 'SHPP'
     ) B

Now, the dates will depend on what the user has selected, but in this case, the B subquery doesn't return anything, so is there something I can do so I can treat like a zero? The PSP_HITS column is a cumlative total, so to get the total for a specific month, I have to take the total at the end of the previous month and subtract it from the total at the end of current month.

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[SantaMufasa]

Yes, Lauren, we can trick Oracle into generating a zero value where no rows exist, using, for example, the following adjustment to your code:

SELECT A.CURR_MONTH - B.LAST_MONTH AS PSPCOUNT
  FROM (SELECT [B][I]nvl(sum(PSP_HITS),0)[/I][/B] AS CURR_MONTH
          FROM V3W_STATS_ARCH_HEAD
         WHERE TRUNC(ARCH_TIMESTAMP) =
               LAST_DAY(TO_DATE('01/05/2007', 'DD/MM/YYYY'))
           AND PLAN_TYPE = 'SHPP'
       ) A
      ,(SELECT [B][I]nvl(sum(PSP_HITS),0)[/I][/B] AS LAST_MONTH
          FROM V3W_STATS_ARCH_HEAD
         WHERE TRUNC(ARCH_TIMESTAMP) =
               LAST_DAY(TO_DATE('01/04/2007', 'DD/MM/YYYY'))
           AND PLAN_TYPE = 'SHPP'
       ) B
/

Let us know if this works well for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[LaurenM]

That works, but why does adding the sum function make it work? I had already tried just using the nvl function but it didn't work.

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[SantaMufasa]

...why does adding the sum function make it work?

When you use any Oracle GROUP/aggregate function (e.g., MAX, MIN, SUM, AVG, COUNT, et cetera), it obligates Oracle to return at least one row, even if the one row contains only NULL as the return value.


In your earlier case (when you did not use an aggregate function), Oracle did not return even one row. When Oracle returns "no rows selected", then there is nothing against which you can apply a NVL function.

(That's why I referred to this method as a trick [which produces a treat for you...Happy Halloween. <grin>])

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[LaurenM]

THANKS!!!

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[Welshbird]

Dave: Your explanations are just excellent, as are your solutions.

Just because I can, and because thargtheslayer and I toasted you at our Seasonal Drinks, have a happy-new-year star.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SantaMufasa]

Thanks, Fee ! You and Tharg (and Lauren) are very generous.

Happy New Year greetings to you all.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]