Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: what does decode(dual.dummy(+),'X',NULL,NULL) do exactly?
"Steve Mitchell" <stevem_at_hdcsi.com> wrote in message
news:8bVx8.2422$B94.556908744_at_newssvr13.news.prodigy.com...
>
> Can somebody help explain why this happens:
>
> SVRMGR> select count(*) from ar_trx_header;
> COUNT(*)
> ----------
> 929
> 1 row selected.
> SVRMGR> select count(*) from ar_trx_header a, dual b where a.artrxhdridx =
> decode(b.dummy(+),'X',NULL,NULL);
> COUNT(*)
> ----------
> 929
> 1 row selected.
> SVRMGR> select count(*) from ar_trx_header a, dual b where a.artrxhdridx =
> NULL;
> COUNT(*)
> ----------
> 0
> 1 row selected.
> SVRMGR> select count(*) from ar_trx_header a, dual b where a.artrxhdridx =
> decode(b.dummy,'X',NULL,NULL);
> COUNT(*)
> ----------
> 0
> 1 row selected.
>
> ..one would think that the DECODE(b.dummy(+)..) would always attempt to
join
> the artrxhdridx to a NULL, which would always return zero rows.
>
> Thanks in advance.
>
In the second select, you did an outer join. That will return all the 929
rows from the first table, even when the second expression is deficient in
rows.
The result is correct.
Paul
Received on Thu Apr 25 2002 - 13:53:20 CDT