Re: Easy DECODE query (hopefully)...

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: Mon, 13 Mar 2000 15:15:34 GMT
Message-ID: <8aj0mi$svs$1@nnrp1.deja.com>

In article <8aiv29$rku$1_at_nnrp1.deja.com>, dolans_at_my-deja.com wrote:
> I have a table:
> FOO
> -------
> ID
> THE_VALUE
>
> If I do a :
> "Select decode(id, NULL, 0, 1) from FOO where THE_VALUE='Mom';"
>
> I get no rows selected. I would like it to return 0 if there is no
> entry in the table for 'Mom'.
>
> Is NULL the correct expression? Yes, I could use count(Id) in the
> search expression instead, but I am trying to return the ID if it
> exists or 0 if it doesn't.
>
> Thanks,
> Sean Dolan
>

If you expect at most 1 rows to be returned from the above -- then something like:

ops$tkyte_at_8i> select nvl( max( object_id ), 0 ) from all_objects where object_name = 'I DO NOT EXIST';

NVL(MAX(OBJECT_ID),0)

ops$tkyte_at_8i> select object_id from all_objects where object_name = 'I DO NOT EXIST'; no rows selected

will do it (as opposed to the second query above showing there was no data). If you do an aggregate with NO group by -- it'll always return 1 row at least/most. You can nvl this result...

> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Thomas Kyte                              tkyte_at_us.oracle.com

Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html --
Opinions are mine and do not necessarily reflect those of Oracle Corp

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Mar 13 2000 - 09:15:34 CST