January 26, 2017 at 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
January 26, 2017 at 10:23 pm
servat - Thursday, January 26, 2017 10:05 PMHow 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
January 26, 2017 at 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.
January 26, 2017 at 11:15 pm
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....
January 27, 2017 at 2:17 am
servat - Thursday, January 26, 2017 10:46 PMI 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 recordsDoes 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.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 27, 2017 at 2:44 am
servat - Thursday, January 26, 2017 10:46 PMI 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 recordsDoes 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
January 27, 2017 at 7:28 am
Eddie Wuerch - Friday, January 27, 2017 2:44 AMservat - Thursday, January 26, 2017 10:46 PMI 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 recordsDoes 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
January 27, 2017 at 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 AMservat - Thursday, January 26, 2017 10:46 PMI 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 recordsDoes 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.
January 27, 2017 at 12:56 pm
servat - Friday, January 27, 2017 11:16 AMI 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 recordsDoes 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 AMservat - Thursday, January 26, 2017 10:46 PMI 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 recordsDoes 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?
January 27, 2017 at 1:27 pm
Eddie Wuerch - Friday, January 27, 2017 2:44 AMservat - Thursday, January 26, 2017 10:46 PMI 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 recordsDoes 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.
January 27, 2017 at 8:32 pm
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