Is is possible put IF inside the WHERE statement

  • How can I do something like this? 


    Select * FROM A
    WHERE
        If Status = 'shipped' THEN
            A.Field = 640 OR A.Field = 540 OR A.Field = 440
        ElseIF Status = 'BackOrder' THEN
            A.Field = 100 OR A.Field = 320
        Else
            A.Field LIKE '%%'
        End

  • servat - Thursday, January 26, 2017 10:05 PM

    How can I do something like this? 


    Select * FROM A
    WHERE
        If Status = 'shipped' THEN
            A.Field = 640 OR A.Field = 540 OR A.Field = 440
        ElseIF Status = 'BackOrder' THEN
            A.Field = 100 OR A.Field = 320
        Else
            A.Field LIKE '%%'
        End

    Convert them to ANDs, ORs, and use parenthesis to separate the cases and control the OR blocks:

    Select * FROM A
    WHERE
         (Status = 'shipped' AND (A.Field = 640 OR A.Field = 540 OR A.Field = 440))
      OR (Status = 'BackOrder' AND (A.Field = 100 OR A.Field = 320))
      OR (Status != 'shipped' AND Status != 'BackOrder' AND A.Field LIKE '%%')

    SQL Server short-circuits the tests, meaning if it passes the first test (Status = 'shipped' AND (A.Field = 640 OR A.Field = 540 OR A.Field = 440)) then it will not proceed to test the conditions following.
    -Eddie

    Eddie Wuerch
    MCM: SQL

  • I don't think that'll do what I want. Let me explain it a little differently. I basically want the WHERE condition to change depending on the if statement. So for example -

    IF Status == 'Shipped', I want WHERE to return all the records where Field = 640 OR Field = 540 OR Field = 440
    and if Status == 'BackOrder',  I want WHERE to return all the records where Field = 100 OR Field = 32
    and if Status is anything else, I want WHERE to return all records

    Does that make better sense? Of course, this is part of a much larger query with multiple joins and all kinds of parameters, so I can't just write it like my example above. Somehow I'm hoping to put the IF THEN in the WHERE clause and have it change the condition.

  • You should use the CASE instead of IF ELSE... This way you can meet your requirement easily. You may try IIF but with limited options....

  • servat - Thursday, January 26, 2017 10:46 PM

    I don't think that'll do what I want. Let me explain it a little differently. I basically want the WHERE condition to change depending on the if statement. So for example -

    IF Status == 'Shipped', I want WHERE to return all the records where Field = 640 OR Field = 540 OR Field = 440
    and if Status == 'BackOrder',  I want WHERE to return all the records where Field = 100 OR Field = 32
    and if Status is anything else, I want WHERE to return all records

    Does that make better sense? Of course, this is part of a much larger query with multiple joins and all kinds of parameters, so I can't just write it like my example above. Somehow I'm hoping to put the IF THEN in the WHERE clause and have it change the condition.

    What is "Status" here? Is it a column in your table or some variable holding a value? Either ways the solution given by Eddie should work.
    If you think it doesn't work, you will need to explain it with some sample data and results for us to understand better.

    Please check the link in my signature if you are unsure about how to provide sample data.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • servat - Thursday, January 26, 2017 10:46 PM

    I don't think that'll do what I want. Let me explain it a little differently. I basically want the WHERE condition to change depending on the if statement. So for example -

    IF Status == 'Shipped', I want WHERE to return all the records where Field = 640 OR Field = 540 OR Field = 440
    and if Status == 'BackOrder',  I want WHERE to return all the records where Field = 100 OR Field = 32
    and if Status is anything else, I want WHERE to return all records

    Does that make better sense? Of course, this is part of a much larger query with multiple joins and all kinds of parameters, so I can't just write it like my example above. Somehow I'm hoping to put the IF THEN in the WHERE clause and have it change the condition.

    Is Status a parameter? Then it works like this:
    CREATE PROC dbo.MyProc(
    @Status varchar(32)  = ' '
    )
    AS
    Select * FROM A
    WHERE
      (@Status = 'shipped' AND (A.Field = 640 OR A.Field = 540 OR A.Field = 440))
    OR (@Status = 'BackOrder' AND (A.Field = 100 OR A.Field = 320))
    OR (@Status != 'shipped' AND @Status != 'BackOrder'); /* this last test will return all rows if Status is anything but 'shipped' or 'BackOrder' */
    RETURN;

    It looks a bit different than your request, but what you see above is the WHERE clause translation of what you have specified.

    -Eddie

    Eddie Wuerch
    MCM: SQL

  • Eddie Wuerch - Friday, January 27, 2017 2:44 AM

    servat - Thursday, January 26, 2017 10:46 PM

    I don't think that'll do what I want. Let me explain it a little differently. I basically want the WHERE condition to change depending on the if statement. So for example -

    IF Status == 'Shipped', I want WHERE to return all the records where Field = 640 OR Field = 540 OR Field = 440
    and if Status == 'BackOrder',  I want WHERE to return all the records where Field = 100 OR Field = 32
    and if Status is anything else, I want WHERE to return all records

    Does that make better sense? Of course, this is part of a much larger query with multiple joins and all kinds of parameters, so I can't just write it like my example above. Somehow I'm hoping to put the IF THEN in the WHERE clause and have it change the condition.

    Is Status a parameter? Then it works like this:
    CREATE PROC dbo.MyProc(
    @Status varchar(32)  = ' '
    )
    AS
    Select * FROM A
    WHERE
      (@Status = 'shipped' AND (A.Field = 640 OR A.Field = 540 OR A.Field = 440))
    OR (@Status = 'BackOrder' AND (A.Field = 100 OR A.Field = 320))
    OR (@Status != 'shipped' AND @Status != 'BackOrder'); /* this last test will return all rows if Status is anything but 'shipped' or 'BackOrder' */
    RETURN;

    It looks a bit different than your request, but what you see above is the WHERE clause translation of what you have specified.

    -Eddie

    Except that "where status is anything else" could include a NULL status, and you may also need to include OR @Status IS NULL.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • I don't think that'll do what I want. Let me explain it a little differently. I basically want the WHERE condition to change depending on the if statement. So for example -

    IF Status == 'Shipped', I want WHERE to return all the records where Field = 640 OR Field = 540 OR Field = 440
    and if Status == 'BackOrder',  I want WHERE to return all the records where Field = 100 OR Field = 32
    and if Status is anything else, I want WHERE to return all records

    Does that make better sense? Of course, this is part of a much larger query with multiple joins and all kinds of parameters, so I can't just write it like my example above. Somehow I'm hoping to put the IF THEN in the WHERE clause and have it change the condition. 

    Eddie Wuerch - Friday, January 27, 2017 2:44 AM

    servat - Thursday, January 26, 2017 10:46 PM

    I don't think that'll do what I want. Let me explain it a little differently. I basically want the WHERE condition to change depending on the if statement. So for example -

    IF Status == 'Shipped', I want WHERE to return all the records where Field = 640 OR Field = 540 OR Field = 440
    and if Status == 'BackOrder',  I want WHERE to return all the records where Field = 100 OR Field = 32
    and if Status is anything else, I want WHERE to return all records

    Does that make better sense? Of course, this is part of a much larger query with multiple joins and all kinds of parameters, so I can't just write it like my example above. Somehow I'm hoping to put the IF THEN in the WHERE clause and have it change the condition.

    Is Status a parameter? Then it works like this:
    CREATE PROC dbo.MyProc(
    @Status varchar(32)  = ' '
    )
    AS
    Select * FROM A
    WHERE
      (@Status = 'shipped' AND (A.Field = 640 OR A.Field = 540 OR A.Field = 440))
    OR (@Status = 'BackOrder' AND (A.Field = 100 OR A.Field = 320))
    OR (@Status != 'shipped' AND @Status != 'BackOrder'); /* this last test will return all rows if Status is anything but 'shipped' or 'BackOrder' */
    RETURN;

    It looks a bit different than your request, but what you see above is the WHERE clause translation of what you have specified.

    -Eddie

    Yes, Status is a parameter. Thanks very much. I will give this a try and let you know how it goes. I appreciate the help guys.

  • servat - Friday, January 27, 2017 11:16 AM

    I don't think that'll do what I want. Let me explain it a little differently. I basically want the WHERE condition to change depending on the if statement. So for example -

    IF Status == 'Shipped', I want WHERE to return all the records where Field = 640 OR Field = 540 OR Field = 440
    and if Status == 'BackOrder',  I want WHERE to return all the records where Field = 100 OR Field = 32
    and if Status is anything else, I want WHERE to return all records

    Does that make better sense? Of course, this is part of a much larger query with multiple joins and all kinds of parameters, so I can't just write it like my example above. Somehow I'm hoping to put the IF THEN in the WHERE clause and have it change the condition. 

    Eddie Wuerch - Friday, January 27, 2017 2:44 AM

    servat - Thursday, January 26, 2017 10:46 PM

    I don't think that'll do what I want. Let me explain it a little differently. I basically want the WHERE condition to change depending on the if statement. So for example -

    IF Status == 'Shipped', I want WHERE to return all the records where Field = 640 OR Field = 540 OR Field = 440
    and if Status == 'BackOrder',  I want WHERE to return all the records where Field = 100 OR Field = 32
    and if Status is anything else, I want WHERE to return all records

    Does that make better sense? Of course, this is part of a much larger query with multiple joins and all kinds of parameters, so I can't just write it like my example above. Somehow I'm hoping to put the IF THEN in the WHERE clause and have it change the condition.

    Is Status a parameter? Then it works like this:
    CREATE PROC dbo.MyProc(
    @Status varchar(32)  = ' '
    )
    AS
    Select * FROM A
    WHERE
      (@Status = 'shipped' AND (A.Field = 640 OR A.Field = 540 OR A.Field = 440))
    OR (@Status = 'BackOrder' AND (A.Field = 100 OR A.Field = 320))
    OR (@Status != 'shipped' AND @Status != 'BackOrder'); /* this last test will return all rows if Status is anything but 'shipped' or 'BackOrder' */
    RETURN;

    It looks a bit different than your request, but what you see above is the WHERE clause translation of what you have specified.

    -Eddie

    Yes, Status is a parameter. Thanks very much. I will give this a try and let you know how it goes. I appreciate the help guys.

    Dumb question, have you even tried the code provided?

  • Eddie Wuerch - Friday, January 27, 2017 2:44 AM

    servat - Thursday, January 26, 2017 10:46 PM

    I don't think that'll do what I want. Let me explain it a little differently. I basically want the WHERE condition to change depending on the if statement. So for example -

    IF Status == 'Shipped', I want WHERE to return all the records where Field = 640 OR Field = 540 OR Field = 440
    and if Status == 'BackOrder',  I want WHERE to return all the records where Field = 100 OR Field = 32
    and if Status is anything else, I want WHERE to return all records

    Does that make better sense? Of course, this is part of a much larger query with multiple joins and all kinds of parameters, so I can't just write it like my example above. Somehow I'm hoping to put the IF THEN in the WHERE clause and have it change the condition.

    Is Status a parameter? Then it works like this:
    CREATE PROC dbo.MyProc(
    @Status varchar(32)  = ' '
    )
    AS
    Select * FROM A
    WHERE
      (@Status = 'shipped' AND (A.Field = 640 OR A.Field = 540 OR A.Field = 440))
    OR (@Status = 'BackOrder' AND (A.Field = 100 OR A.Field = 320))
    OR (@Status != 'shipped' AND @Status != 'BackOrder'); /* this last test will return all rows if Status is anything but 'shipped' or 'BackOrder' */
    RETURN;

    It looks a bit different than your request, but what you see above is the WHERE clause translation of what you have specified.

    -Eddie

    You're a genius. Worked like a champ. I appreciate it very much. Sorry, it was late last night and your first example didn't click in my head. Thanks very much for the help.

  • For a performance reasons, if you're going to code a query with multiple conditional predicated in the WHERE clause, then understand it probably would not be 'sargable', so make the columns in resultset as narrow as possible and consider a "covering index".

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 11 posts - 1 through 10 (of 10 total)

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