Re: Easy DECODE query (hopefully)...

<dolans_at_my-deja.com> wrote in message news:8aiv29$rku$1_at_nnrp1.deja.com...
> 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.

Yes. It needs some trick:
(On PO8.0.4 & Windows 98)

SQL> create table foo (id number, the_value varchar2(8));

Table created.

SQL> select decode(id, null, 0, 1)
  2 from foo, (select 'Mon' criterion from dual)   3 where the_value(+)=criterion;

DECODE(ID,NULL,0,1)

                  0