Re: Do all the legs of an "OR" with null comparisons get executed?

On Mar 28, 10:53 am, Arthernan <arther..._at_hotmail.com> wrote:
> If I do an explain plan of the statement below I can see that the
> "and" clauses that have MMDDOB=NULL do not get executed
>
> select * from v_search where
> UPP_FIRST='JOHN' and UPP_LAST='SMITH' and MMDDOB=NULL and AA=1 or
> UPP_FIRST='JOHN' and UPP_LAST='SMITH' and BB=2 or
> UPP_FIRST='JOHN' and UPP_LAST='SMITH' and CC='CA' or
> UPP_FIRST='JOHN' and MMDDOB=NULL and AA=1 and BB=2 or
> UPP_FIRST='JOHN' and MMDDOB=NULL and AA=1 and CC='CA' or
> UPP_FIRST='JOHN' and BB=2 and CC='CA' or
> UPP_LAST='SMITH' and MMDDOB=NULL and AA=1 and BB=2 or
> UPP_LAST='SMITH' and MMDDOB=NULL and AA=1 and CC='CA' or
> UPP_LAST='SMITH' and BB=2 and CC='CA' or
> MMDDOB=NULL and AA=1 and BB=2 and CC='CA'
>
> My problem is how to know if it does the same thing when I parametize
> the query as
>
> select * from v_search where
> UPP_FIRST=:UPP_FIRST and UPP_LAST=:UPP_LAST and MMDDOB=:MMDD and
> AA=:AA or
> UPP_FIRST=:UPP_FIRST and UPP_LAST=:UPP_LAST and BB=:BB or
> UPP_FIRST=:UPP_FIRST and UPP_LAST=:UPP_LAST and CC=:CC or
> UPP_FIRST=:UPP_FIRST and MMDDOB=:MMDD and AA=:AA and BB=:BB or
> UPP_FIRST=:UPP_FIRST and MMDDOB=:MMDD and AA=:AA and CC=:CC or
> UPP_FIRST=:UPP_FIRST and BB=:BB and CC=:CC or
> UPP_LAST=:UPP_LAST and MMDDOB=:MMDD and AA=:AA and BB=:BB or
> UPP_LAST=:UPP_LAST and MMDDOB=:MMDD and AA=:AA and CC=:CC or
> UPP_LAST=:UPP_LAST and BB=:BB and CC=:CC or
> MMDDOB=:MMDD and AA=:AA and BB=:BB and CC=:CC
>
> Any ideas?

Nothing equals NULL, so possibly you should change your logic to use the nvl() function to provide some 'usable' value when the NULLs appear.

As to your other question I'll wait until you make the code changes and report on the results of those modifications.

David Fitzjarrell Received on Fri Mar 28 2008 - 11:41:09 CDT