Post reply

WHERE clause

  • June 19, 2008 at 1:17 pm

    #192549

    I'm trying to filter my WHERE clause:

    WHERE (c.CustomerID = '3942') AND (ol.DetailTableID '6/17/2008') AND (o.Opendate < '6/19/2008') AND (ol.PLU LIKE 'ONL-ANN%') OR (ol.PLU like 'ONL-EX%') OR (ol.PLU like 'ONL-ZOO%') OR (ol.PLU like 'ONL- WILD%') OR (ol.PLU like 'ONL - Mini%') OR (ol.PLU like 'ONL - Bricks')

    ORDER BY o.Opendate ASC

    Thats obviously a lot of AND/OR statements. It returns data but does not filter by date. How can I simplify this by searching with these parameters? Thanks for all your help!

  • June 19, 2008 at 1:26 pm

    #831355

    I think you have an operator percedence problem: Your ORs stand on their own, neglecting the other predicates.

    WHERE (c.CustomerID = '3942')

    AND (ol.DetailTableID '6/17/2008')

    AND (o.Opendate < '6/19/2008')

    AND (ol.PLU LIKE 'ONL-ANN%')

    OR (ol.PLU like 'ONL-EX%')

    OR (ol.PLU like 'ONL-ZOO%')

    OR (ol.PLU like 'ONL- WILD%')

    OR (ol.PLU like 'ONL - Mini%')

    OR (ol.PLU like 'ONL - Bricks')

    ORDER BY o.Opendate ASC

    maybe Should be

    WHERE (c.CustomerID = '3942')

    AND (ol.DetailTableID '6/17/2008')

    AND (o.Opendate < '6/19/2008')

    AND (

    (ol.PLU LIKE 'ONL-ANN%')

    OR (ol.PLU like 'ONL-EX%')

    OR (ol.PLU like 'ONL-ZOO%')

    OR (ol.PLU like 'ONL- WILD%')

    OR (ol.PLU like 'ONL - Mini%')

    OR (ol.PLU like 'ONL - Bricks')

    )

    ORDER BY o.Opendate ASC

    btw try to avoid implicit conversions (c.customerID is INT ?? use c.CustomerID = 3942 )

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • June 19, 2008 at 1:28 pm

    #831358

    You need to properly nest your Ands and Ors.

    This might do what you need:

    (c.CustomerID = '3942') AND (ol.DetailTableID < '6/17/2008') AND (o.Opendate < '6/19/2008') AND

    ((ol.PLU LIKE 'ONL-ANN%')

    OR (ol.PLU like 'ONL-EX%')

    OR (ol.PLU like 'ONL-ZOO%')

    OR (ol.PLU like 'ONL- WILD%')

    OR (ol.PLU like 'ONL - Mini%')

    OR (ol.PLU like 'ONL - Bricks'))

    (I noticed that your first date comparison, DetailTableID, doesn't seem to have a comparitor in it. I used less than, but you'll want to make sure that's correct.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • June 19, 2008 at 1:31 pm

    #831368

    That worked! Thanks you so much.

  • June 19, 2008 at 1:33 pm

    #831370

    it seems like a silly thought but make sure that the date column actually has data.

    Everything you can imagine is real.

  • June 19, 2008 at 1:43 pm

    #831380

    hayess (6/19/2008)

    I'm trying to filter my WHERE clause:

    WHERE (c.CustomerID = '3942') AND (ol.DetailTableID '6/17/2008') AND (o.Opendate < '6/19/2008') AND (ol.PLU LIKE 'ONL-ANN%') OR (ol.PLU like 'ONL-EX%') OR (ol.PLU like 'ONL-ZOO%') OR (ol.PLU like 'ONL- WILD%') OR (ol.PLU like 'ONL - Mini%') OR (ol.PLU like 'ONL - Bricks')

    ORDER BY o.Opendate ASC

    Thats obviously a lot of AND/OR statements. It returns data but does not filter by date. How can I simplify this by searching with these parameters? Thanks for all your help!

    I think the problem you are having is with your parenthesis. Let's modify the criteria as:

    WHERE c.CustomerID = '3942'

    AND ol.DetailTableID >= '6/17/2008'

    AND o.Opendate < '6/19/2008'

    AND (ol.PLU LIKE 'ONL-ANN%'

    OR ol.PLU like 'ONL-EX%'

    OR ol.PLU like 'ONL-ZOO%'

    OR ol.PLU like 'ONL- WILD%'

    OR ol.PLU like 'ONL - Mini%'

    OR ol.PLU like 'ONL - Bricks')

    ORDER BY o.Opendate ASC

    And see if this gets you closer to what you are looking for.

    Jeff

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply