Re: Easy DECODE query (hopefully)...

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Mon, 13 Mar 2000 18:32:27 +0100
Message-ID: <8akrbd$2bkh$3@news5.isdnet.net>

You can't do it with decode and null. If a query returns no row you can't have anything else than... nothing ;-)

May be you can use an outer join to dual:

v734> create table t (col number);
v734> insert into t values (1);
v734> insert into t values (2);
v734> insert into t values (4);
v734> commit;
v734> select nvl(col,0) col

  2 from t, dual
  3 where to_char(col(+))<>dummy
  4 and col(+) = 1
  5 /

COL

1 row selected.

v734> select nvl(col,0) col
  2 from t, dual
  3 where to_char(col(+))<>dummy
  4 and col(+) = 3
  5 /

COL

1 row selected.

--
Have a nice day
Michel

<dolans_at_my-deja.com> a écrit dans le message : 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.
>
> Thanks,
> Sean Dolan
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Mar 13 2000 - 11:32:27 CST