Re: problem with sql-statement

henner.hucke_at_rolls-royce.com (Henner Hucke) wrote in message news:<f6796be6.0305070509.2882672f_at_posting.google.com>...
> Hello,
>
> I have a problem with the following SQL-Statement:
> (Oracle 8.1.7.4 on Solaris 2.6)
>
> If I run
> select mbd_number from M10000 where
> (
> UPPER(MBD_WORKFLOWEBENE) = 'AUSSTELLER'
> AND
> MBD_AUSSTELLER_STNR = '000000'
> )
> OR
> (
> UPPER(MBD_FEHLERGRUPPE) = 'AUSSTELLER'
> AND
> MBD_AUSSTELLER_STNR = '000000'
> AND
> UPPER(MBD_WORKFLOWEBENE) = 'AUSSTELLER'
> )
> I get 'no rows selected'
>
> If I run
>
> select mbd_number from M10000 where
> (
> UPPER(MBD_WORKFLOWEBENE) = 'AUSSTELLER'
> AND
> MBD_AUSSTELLER_STNR = '000000'
> )
>
> I get one row presented.
>
> I have no idea where my error is.
> Please can someone help me?
> Thanks.

Henner, I say your second post (separate thread) first and traced it back to this one so someone may have alreay pointed this out but as written Condition A must be true for condition B to be true since both condition A tests are included in B as part of an and condition. Therefore there is no reason to test for condition B at all; it will only be true when A is true and if A is true there is no need to test condition B at all.

The optimizer probably recognizes the inclusion and tried to rewrite the statement, but the bug listed in your second post (that I did not track down at metalink) means the optimizer screwed up. But you can eliminate the unnecessary condition and all should be well.

HTH -- Mark D Powell -- Received on Thu May 08 2003 - 09:16:20 CDT