Re: SQL Default Return

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Tue, 25 Feb 2003 22:20:49 -0500
Message-ID: <v5oe3q905tvhea@corp.supernews.com>

If the date does not exist in the calendar table ... it is not even considered in the decode statement! If a row exists in the calendar table and the date column is null then your "Date not exists" will be thrown.

and please tell me that you are not creating column names like "date"

Try this:
select DECODE(count(*), 0, 'Date NOT exists','Date Exists') from calendar
where trunc(mydate) = to_date('01-JAN-2003','dd-mon-yyyy') /

or use the CASE statement. DECODE might be deprecated in future versions!

select

   CASE

         WHEN count(*) = 0 THEN 'Date Not Exists'
          ELSE 'Date Exists'

   END as isdatethere
from calendar
where trunc(mydate) = to_date('01-JAN-2003','dd-mon-yyyy') /

Anurag

"abc" <abc_at_abc.com.au> wrote in message news:b3h2lq$dcq$1_at_mws-stat-syd.cdn.telstra.com.au...
> Hi,
>
> I have a simple SQL
>
> SELECT DECODE(a.date, NULL, 'Date Not Exists', a.date) as Date FROM CALENDAR
> WHERE date = '01-JAN-2003'
>
> '01-JAN-2003' is not in CALENDAR Table then I must expect a Default return
> as 'Date Not Exists'. But that doesn't work.
>
> Anyone knows how to get this Default Return if No rows return.
>
> Thanks
>
>
Received on Tue Feb 25 2003 - 21:20:49 CST