#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    3
    Rep 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!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,382
    Rep Power
    391
    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
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    3
    Rep 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.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    3
    Rep Power
    0
    Solved my 2nd question, thanks

IMN logo majestic logo threadwatch logo seochat tools logo