none
Where statement not working as expected

    Question

  • I have this at the end of my SQL statement...

    WHERE 
    
    (TABLEA.C_ONUM IS NULL
    OR TABLEA.C_DNUM IS NULL)
    OR (TABLEA.C_ONUM not like '%IG%'
    AND TABLEA.C_DNUM not like '%IG%'
    AND TABLEA.C_ONUM not like '%X%'
    AND TABLEA.C_DNUM not like '%X%'
    AND TABLEA.C_ONUM not like '%AF%'
    AND TABLEA.C_DNUM not like '%AF%')
    OR ( ((TABLEB.Division like '%AB%' OR TABLEB.Division like '%FG%')
    AND (TABLEA.VNAME not like '%City Of%' OR TABLEA.VNAME not like '%County Of%')))

    ONUM      DNUM             Division            VNAME

    123           453                 Audit               Steve's INC

    123           IG8                 Sales               M&M Co.

    AF2           435                 HR                   Pimlico

    7826         5X8                 ABC                  City Of York

    892                              FG-Top            County of Warwick

    Yields....

    ONUM    DNUM                 Division            VNAME

    123           453                 Audit               Steve's INC

    892                            FG-Top            County of Warwick

    This second row shouldn't be showing up...

    Tuesday, June 25, 2013 3:22 PM

All replies

  • Hello,

    You'r open and close bracket is not right ,you can try below given code it will work .

    TABLEA.C_ONUM IS NULL
    OR TABLEA.C_DNUM IS NULL
    OR (TABLEA.C_ONUM not like '%IG%')
    AND (TABLEA.C_DNUM not like '%IG%'
    AND TABLEA.C_ONUM not like '%X%'
    AND TABLEA.C_DNUM not like '%X%'
    AND TABLEA.C_ONUM not like '%AF%'
    AND TABLEA.C_DNUM not like '%AF%')
    OR ( ((TABLEB.Division like '%AB%' OR TABLEB.Division like '%FG%')
    AND (TABLEA.VNAME not like '%City Of%' OR TABLEA.VNAME not like '%County Of%')))

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Thanks & Regards,

    Gaurav Badhani.

    Tuesday, June 25, 2013 3:33 PM
  • Brackets changed nothing.

    I'm still getting rows like this one... 

    ONUM      DNUM             Division            VNAME

    892                              FG-Top            County of Warwick


    The last condition,  Divisions like AB or FG but not vname of "City of" or "County of" doesn't seem to be working right.
    • Edited by DCDeez Tuesday, June 25, 2013 3:42 PM extra detail
    Tuesday, June 25, 2013 3:41 PM
  • Is DNUM = NULL?

    OR TABLEA.C_DNUM IS NULL)

    Tuesday, June 25, 2013 3:49 PM
  • Yes sorry the DNUM is Null not " "...

    ONUM      DNUM             Division            VNAME

     892         NULL               FG-Top           County of Warwick

    Tuesday, June 25, 2013 3:57 PM