Creating a Case Statement

Question

Post reply

Creating a Case Statement

Potso

SSChasing Mays

Points: 657
More actions
June 19, 2009 at 9:56 am

#95598

I am writing code in SSRS and I had if statements in the code section of the Report. But I realized for the report to work correctly I need to do it in the actually SQL query,
What I need to do is convert the following if statements to Case statements and set them equal to a variable that I will add into the table:
If statements:
IF (ILCategory=3 or ILCategory=4) AND SCCategory =1 THEN
Return ("Refile")
end if
IF (ILCategory=3 or ILCategory=4) AND SCCategory = 2 THEN
Return("Retrieve")
end if
IF (ILCategory=3 or ILCategory=4)AND SCCategory =3 THEN
Return ("Remove")
end if
SO what I need to do is have a declared variable @Category in the SQL query, but i cant figure out how to have that variable in my Select statement so its in with the data, and also run the Case statement to determine what the value should be.... I have alot more if statements then this but if someone can just show me a sample I know i can do all the rest. Any help would be great... THANKS!

Viewing 2 posts - 1 through 1 (of 1 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

something like this should get you started:

SELECT

@Category As TheSelectedCategory,

CASE

WHEN ILCategory IN (3,4) AND SCCategory =1

THEN 'Refile'

WHEN ILCategory IN (3,4) AND SCCategory =2

THEN 'Retrieve'

WHEN ILCategory IN (3,4) AND SCCategory =3

THEN 'Remove'

ELSE 'Blank'

END As TheResults,

OtherColumns

FROM YOURTABLE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!