Re: SQL Default Return

From: Martin Burbridge <pobox002_at_bebub.com>
Date: 26 Feb 2003 05:41:21 -0800
Message-ID: <45a06b65.0302260541.559d6cbd_at_posting.google.com>

"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.
>

You can force a null using an aggregate function, in this example it has no other purpose.

SQL> select * from t;

D

---

26-FEB-03
25-FEB-03 SQL> select decode(max(d), null, 'not found', max(d))   2 from t where trunc(d) = to_date('02-25-2003','mm-dd-yyyy');

DECODE(MA

---

25-FEB-03 SQL> select decode(max(d), null, 'not found', max(d))   2 from t where trunc(d) = to_date('02-24-2003','mm-dd-yyyy');

DECODE(MA

---

not found

Hth

Martin Received on Wed Feb 26 2003 - 14:41:21 CET