oracle case or decode? - how to handle no recs

How can I make sure in this case statement that if I get now rows selected that I set that my result=1? I want a value of 1 if I have no records or a count of 0. I would also want a null date to be sysdate by default.

Here is what I have so far. It works with (sysdate-1), but when I tested it with (sysdate-0) or =sysdate (today's date), I was getting no records. So I want to be able to handle null values too.

How can I change this query to do that? or would I use something like DECODE?

select 
     to_date(year||'/'||month||'/'||day, 'YYYY/MM/DD') as mydt,
     case when count(*) = 0 then 1 else 0 end result
from Table1 
where 
   to_date(year||'/'||month||'/'||day, 'YYYY/MM/DD') >= trunc(sysdate)-1
GROUP BY 
    to_date(year||'/'||month||'/'||day, 'YYYY/MM/DD')

Here is my table desc. Perhaps, I should go off of timestamp instead? There is also a value of counts which may help change this query or not. So what i'm looking for is coming up with a value of 1 or 0 with a timestamp if that's possible.

SQL> desc Table1
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 YEAR                           NUMBER
 QUARTER                        NUMBER
 MONTH                          NUMBER
 DAY                            NUMBER
 HOUR                           NUMBER
 TIMESTAMP                      NUMBER
 CNT_N                              NUMBER
 ACTN_N                             NUMBER 
 ADDR_C            开发者_JS百科           VARCHAR2(255)

You will never get a count(*) of zero with a group by. If there are no rows, you won't get any groups and if there are rows then any group you get will show the number of rows.

If you have 3 rows for January 1st and another 3 for January 3rd, you will get :

2011/01/01 3
2011/01/03 3

You won't get a row for January 2nd. Are you wanting to generate that row ? Is there a maximum number of rows that you want returned ?

I think you can do modify your query like below to assign the sysdate to null values

select
       nvl(to_date(year||'/'||month||'/'||day, 'YYYY/MM/DD') ,sysdate) as mydt
from Table1  
where
       nvl(to_date(year||'/'||month||'/'||day, 'YYYY/MM/DD') ,sysdate) 
                      >= trunc(sysdate)-1 
GROUP BY      nvl(to_date(year||'/'||month||'/'||day, 'YYYY/MM/DD') ,sysdate)

but your second requirement is not very much understood.... please clarify what exectly you want with count(*)

if you want to assign 1 for null date count then you can add following line in select statment

case 
when nvl(to_date(year||'/'||month||'/'||day, 'YYYY/MM/DD') ,sysdate) = sysdate then
     1
else
count(*)-- or 0 ???!!!!!

It looks like you need to generate the date(s) and then see how may records match each one. Assuming you don't have any data in the future you can do something like:

select trunc(sysdate) + level - (:range + 1) as dt
from dual
connect by level <= (:range + 1);

which with :range set to 1 gives two dates:

DT
---------
19-JUL-11
20-JUL-11

You can then have outer join to that list of dates:

with tmp_dt as (
    select trunc(sysdate) + level - (:range + 1) as dt
    from dual
    connect by level <= (:range + 1)
)
select td.dt as mydt,
    case when count(t.year) = 0 then 1 else 0 end as result
from tmp_dt td
left join table1 t on t.year = extract(year from td.dt)
    and t.month = extract(month from td.dt)
    and t.day = extract(day from td.dt)
group by td.dt
order by td.dt;

If I only have any data in the table for 19-Jul-11, I get:

MYDT          RESULT
--------- ----------
19-JUL-11          0
20-JUL-11          1

If you do have data in the future this won't show it; range is how many days to look in the past. If you know there's a limit of, say, seven days you can use connect by level <= (:range + 1) + 7 or have a second variable, but it rather depends on what you want to see.

I've swapped the join around a bit to avoid doing a date conversion for every row in the table, extracting the relevant part of the generated date instead. I hope you have a reason for storing the date components in separate fields rather than as a date.

If you're only looking for data from today, just change the date generator:

with tmp_dt as (select trunc(sysdate) as dt from dual)
select td.dt as mydt,
     case when count(t.year) = 0 then 1 else 0 end result
from tmp_dt td
left join table1 t on t.year = extract(year from td.dt)
    and t.month = extract(month from td.dt)
    and t.day = extract(day from td.dt)
group by td.dt;

If you always want yesterday, it would be select trunc(sysdate) - 1 as dt from dual, etc.

May be you can encapsulate your whole query within the NVL function like this

SELECT NVL(TO_CHAR((select to_date(year || '/' || month || '/' || day,
                                  'YYYY/MM/DD') as mydt
                     from Table1
                    where to_date(year || '/' || month || '/' || day,
                                  'YYYY/MM/DD') >= trunc(sysdate) - 1
                    GROUP BY to_date(year || '/' || month || '/' || day,
                                     'YYYY/MM/DD')),
                   'DD/MM/YYYY HH:MI:SS AM'),
           '1')
  FROM DUAL

I have removed the case statement from the query Instead , what this query will do is, in case your query does return any value, it will do so without interference. However, in case the query returns NOTHING, then the NVL function will take over and return 1 I have used the TO_CHAR function to maintain the datatype of both the arguments in the NVL function. Both the date returned and the value for NVL are character based

Hope it helps