Re: what does decode(dual.dummy(+),'X',NULL,NULL) do exactly?

Because the outer join within a function is interpreted to operate like this (but this isn't proper syntax) ...

where a.artrxhdridx = decode(b.dummy,'X',NULL,NULL) (+);

This isn't special to 'decode', any function will do the same thing. To answer your question, that expression does nothing. Nothing can equal null so no join can occur. Therefore, the outer will allow each ar_trx_header row to be returned.

Richard

Steve Mitchell wrote:
>
> 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.
>
> --steve
Received on Thu Apr 25 2002 - 13:13:15 CDT