April 25, 2012 at 6:57 am
I have a query with a case statement the case statement is not working . I am trying to see if col1-col3 has a zero value
3 columns in the table are
Col1
Col2
Col3
My query is
DECLARE @var AS VARCHAR(4)
SET @var = 'TST2'
SELECT
Col5
,Col6
,Col7
WHERE Col7 ='A'
AND Col6='S'
AND Case WHEN @var = 'TST1'
THEN Col1 =0
WHEN @var ='TST2'
THEN Col2 =0
ELSE
Col3=0
END
April 25, 2012 at 7:25 am
Hi
In what way is it not working what are you trying to achieve?
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 25, 2012 at 7:31 am
This code with the variable set as 'TST2' is essentially doing this:
SELECT Col5
,Col6
,Col7
FROM table
WHERE Col7 = 'A'
AND Col6 = 'S'
AND Col2 = 0 --kind of, your syntax is wrong
Not to mention, you said your table has col1, col2, and col3... So where are col5, col6, and col7 coming from? This is also syntactically wrong...
What do you want it to do? Please provide DDL for the table and sample data as illustrated in the article by Jeff Moden in my signature.
Jared
CE - Microsoft
April 25, 2012 at 7:31 am
This should help..
DECLARE @var AS VARCHAR(4)
SET@var = 'TST2'
SELECTCol5, Col6, Col7
FROMTableName -- You missed the TableName
WHERECol7 = 'A'
ANDCol6 = 'S'
AND1 = CASE
WHEN @var = 'TST1' AND Col1 = 0 THEN 1
WHEN @var = 'TST2' AND Col2 = 0 THEN 1
WHEN Col3 = 0 THEN 1
ELSE 0
END
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 25, 2012 at 7:36 am
You've misunderstood what a Case statement is for in T-SQL. It's a common confusion. What you wrote looks like Visual Basic or some language related to that.
In SQL, Case doesn't work that way. It returns a value based on a test.
So, you can do this:
case when @Var = 1 then 1
but you can't do this:
case when @Var = 1 then MyColumn = 1
The difference is that "MyColumn = 1" is not a "value" that can be returned, while "1" is.
To see how it works, try this:
select
CASE when 1 = 0 then 'False'
when 1 = 1 then 'True'
end;
Also, as pointed out, you're missing a From clause in your query. You can only skip the From clause if you are selecting values (variables or static values), instead of columns. Have to have a From clause if you want any columns. Or if you want a Where clause.
- 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
April 25, 2012 at 7:44 am
Thank You Kingston...
Works Great!
April 25, 2012 at 9:34 am
I don't think that case work in where clause, But I didn't check it. You can rewrite the condition as following:
My query is
DECLARE @var AS VARCHAR(4)
SET @var = 'TST2'
SELECT
Col5
,Col6
,Col7
WHERE Col7 ='A'
AND Col6='S'
AND ( (@var = 'TST1' AND Col1 =0)
OR (@var ='TST2' AND Col2 =0)
OR Col3=0
)
END
April 25, 2012 at 9:44 am
m.eissa85 (4/25/2012)
I don't think that case work in where clause, But I didn't check it. You can rewrite the condition as following:My query is
DECLARE @var AS VARCHAR(4)
SET @var = 'TST2'
SELECT
Col5
,Col6
,Col7
WHERE Col7 ='A'
AND Col6='S'
AND ( (@var = 'TST1' AND Col1 =0)
OR (@var ='TST2' AND Col2 =0)
OR Col3=0
)
END
Case works just fine in a Where clause, if you use it correctly.
Works in Order By too. Or Group By, Having, or Select. Works in From clause if it's used in Join conditions (as opposed to trying to use it to pick which table to join to).
- 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
April 25, 2012 at 11:15 pm
m.eissa85 (4/25/2012)
I don't think that case work in where clause, But I didn't check it. You can rewrite the condition as following:My query is
DECLARE @var AS VARCHAR(4)
SET @var = 'TST2'
SELECT
Col5
,Col6
,Col7
WHERE Col7 ='A'
AND Col6='S'
AND ( (@var = 'TST1' AND Col1 =0)
OR (@var = 'TST2' AND Col2 =0)
OR Col3=0
)
END
Good attempt, but there is a small mistake here
You will have to change the code a bit to make it work the way the OP wants it to
AND(
( @var = 'TST1' AND Col1 = 0 )
OR ( @var = 'TST2' AND Col2 = 0 )
OR ( @var NOT IN ( 'TST1', 'TRT2' ) AND Col3 = 0 )
)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 26, 2012 at 3:33 am
Thanks
GSquared, Kingston Dhasian
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply