Need help with conditional column

Question

Need help with conditional column

tesker

SSC Rookie

Points: 34
More actions
March 25, 2010 at 2:14 pm

#146117

I've found several examples of how to use the CASE statement to do this but for some reason I cannot get it to work. Here is my SQL:
SELECT client_ind, first_name, last_name,
CASE WHEN client_ind = 'Y'
THEN picklist1ms AS CaseType
ELSE '' AS CaseType
FROM dbo.vCrystalDailyReport2 INNER JOIN
dbo.custom_caseinfo ON dbo.vCrystalDailyReport2.case_sk = dbo.custom_caseinfo.case_sk
It works until I insert the conditional logic and then I get errors. Can anyone tell me what I'm doing wrong? I need the CaseType column to display the value of picklist1ms if the value of client_ind is "Y', otherwise the the CaseType column should be blank.
Thanks!
Tom Esker

Viewing 8 posts - 1 through 7 (of 7 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

tesker (3/25/2010)
I've found several examples of how to use the CASE statement to do this but for some reason I cannot get it to work. Here is my SQL:
SELECT client_ind, first_name, last_name,
CASE WHEN client_ind = 'Y'
THEN picklist1ms AS CaseType
ELSE '' AS CaseType
FROM dbo.vCrystalDailyReport2 INNER JOIN
dbo.custom_caseinfo ON dbo.vCrystalDailyReport2.case_sk = dbo.custom_caseinfo.case_sk
It works until I insert the conditional logic and then I get errors. Can anyone tell me what I'm doing wrong? I need the CaseType column to display the value of picklist1ms if the value of client_ind is "Y', otherwise the the CaseType column should be blank.
Thanks!
Tom Esker

1. the CASE statement needs an END

2. you might want to try aliasing the column outside of the CASE statement:

CaseType = CASE when client_ind = 'Y' then picklist1ms

else ''

END

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

tesker SSC Rookie Points: 34 More actions · Answer 2

Thanks. I actually did have an END in my real query but neglected to include it in the shortened version for this post. I just added END and I still get errors using below SQL:

SELECT client_ind, first_name, last_name,

CASE WHEN client_ind = 'Y'

THEN picklist1ms AS CaseType

ELSE '' AS CaseType

END

FROM dbo.vCrystalDailyReport2 INNER JOIN

dbo.custom_caseinfo ON dbo.vCrystalDailyReport2.case_sk = dbo.custom_caseinfo.case_sk

I also tried it with ELSE '' without the AS CaseType and still get:

Error in SELECT clause: expression near 'AS'.

Error in SELECT clause: expression near 'FROM'.

Missing FROM clause.

Unable to parse query text.

It runs fine as:

SELECT client_ind, first_name, last_name, picklist1ms AS CaseType

FROM dbo.vCrystalDailyReport2 INNER JOIN

dbo.custom_caseinfo ON dbo.vCrystalDailyReport2.case_sk = dbo.custom_caseinfo.case_sk

So the problem has to be with the syntax of the conditional part.

Any suggestions?

John Rowan SSC Guru Points: 56440 More actions · Answer 3

The alias declaration must come after the END....

....END AS CaseType

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

LutzM SSC Guru Points: 107049 More actions · Answer 4

instead of

CASE WHEN client_ind = 'Y'

THEN picklist1ms AS CaseType

ELSE '' AS CaseType

END

it needs to be

CASE WHEN client_ind = 'Y'

THEN picklist1ms

ELSE ''

END AS CaseType

Edit: ... just like John already suggested... 🙂

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

I actually did have an END in my real query but neglected to include it in the shortened version for this post.

In the future, keep in mind that people's answers are based on what you post. Garbage in/garbage out. Please show your exact code, not "something that sorta kinda looks like this..." 😉 You'll get the answer you need quicker.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

tesker SSC Rookie Points: 34 More actions · Answer 6

Thanks guys. With your help I got it to work. The problem was that I was using one CASE statement to try to display 3 conditional columns. I did 3 separate CASE statements - one for each column with AS <column name> after END with each one and now it works.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

Good job! Glad its working for you now.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills