help with oracle case statement

So far I have this case statement working where I basically assign a value of 1 or 0 to the result. Now, I want to tweak this and add a second column (date information).

Here's the working case statement now:

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

Result is:

RESULT
----------
     0

Now, I want to get that same date info (to_date(year||'/'||month||'/'||day, 'YYYY/MM/DD') >= trunc(sysdate)-1) in the same result set.

Is there a way to do this in one 开发者_如何学Pythonpass? Result I would like to get:

MYDT     RESULT
------   ---------
18-JUL-11      0

---

There are a couple of ways to do this depending on your data.

For example if you can have multiple values for the date but always want a count of * you could do

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

SELECT DISTINCT   
       to_date(year||'/'||month||'/'||day, 'YYYY/MM/DD') MYDT,
        totalcount.result
FROM 
     Table1 , Totalcount 
where 
   to_date(year||'/'||month||'/'||day, 'YYYY/MM/DD') >= trunc(sysdate)-1

However if you want to count by Date you'd need to do

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')

---

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;