Query results ignoring multiple WHERE/AND funtions

Dev Shed Forums Sponsor:

January 18th, 2013, 10:54 AM

bbrunof

Registered User

Join Date: Jan 2013

Posts: 3

Time spent in forums: 1 h 9 m 39 sec
Reputation Power: 0

Query results ignoring multiple WHERE/AND funtions

I am trying to pull from multiple tables, across two databases, with multiple where/and functions but the results are not obeying all the where/and functions. Here is the code:

select a.PRACCT, a.PRSTAT, b.NStat, c.DISPCODE
FROM ODS.dbo.tblZTDPPRI a
inner join ODS.dbo.tblNotes b
ON a.PRACCT=b.Account_Number
inner join LSDM.dbo.tblAscensionReturnFile c
ON a.PRACCT=c.AccountNumber
WHERE b.NSTAT='W'
AND a.PRSTAT<>'B01' AND a.PRSTAT<>'B02' AND a.PRSTAT<>'B03' AND a.PRSTAT<>'B04' AND a.PRSTAT<>'B05' AND a.PRSTAT<>'B06' AND a.PRSTAT<>'B07' AND a.PRSTAT<>'B08' AND a.PRSTAT<>'B09' AND a.PRSTAT<>'B10' AND a.PRSTAT<>'B11' AND a.PRSTAT<>'B12' AND a.PRSTAT<>'B13' AND a.PRSTAT<>'B14' AND a.PRSTAT<>'B15' AND a.PRSTAT<>'B16' AND a.PRSTAT<>'B17' AND a.PRSTAT<>'B18' AND a.PRSTAT<>'B19' AND a.PRSTAT<>'B20' AND a.PRSTAT<>'B33' AND a.PRSTAT<>'B34' AND a.PRSTAT<>'B36' AND a.PRSTAT<>'B37' AND a.PRSTAT<>'B95'
AND c.DISPCODE='30' or c.DISPCODE='2' or c.DISPCODE='88'

The results are in line with the 'DISPCODE' rule, returning only 30, 2, and 88 values. However, the results are returning incorrect values for PSTAT and NStat. For instance, some rows have a PSTAT of 'B95' and/or NStat of 'K' even though, I think, the query should not allow it. Any help is appreciated!

January 18th, 2013, 11:10 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 2,349

Time spent in forums: 1 Month 4 Days 7 h 17 m 46 sec
Reputation Power: 390

This is a problem of precedence, use parenthesis to ensure the right evaluation order

Code:

   and (c.DISPCODE = 30
    or  c.DISPCODE = 2
    or  c.DISPCODE = 88)

The above code can be simplified as

Code:

   and c.DISPCODE in (30,2,88)

and likewise for the comparisons for the PRSTAT column

Comments on this post

bbrunof agrees!

January 18th, 2013, 11:27 AM

bbrunof

Registered User

Join Date: Jan 2013

Posts: 3

Time spent in forums: 1 h 9 m 39 sec
Reputation Power: 0

Awesome.. worked, thanks.

Is there a reason why the DISPCODE condition required parenthesis to work and the others didnt?

And, I am receiving an error when trying to simplify the PRSTAT conditions. This is what I am trying:

AND a.PRSTAT not in (B01,B02,...)

Error says that B01 is an invalid column name. Simplifying worked with the DISPCODE condition you suggested.

January 18th, 2013, 11:52 AM

bbrunof

Registered User

Join Date: Jan 2013

Posts: 3

Time spent in forums: 1 h 9 m 39 sec
Reputation Power: 0

Solved my 2nd question, thanks

Query results ignoring multiple WHERE/AND funtions

Developer Shed Advertisers and Affiliates