Case statement

Question

Case statement

Viewing 3 posts - 16 through 17 (of 17 total)

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

You did not give your case statement a column name: you have to either give it an input expression or name it.

You have to change this:

CASE
When SalesProb = 0 then 10
When SalesProb = 1 then 20
When SalesProb = 2 then 30
When SalesProb = 3 then 40
When SalesProb = 4 then 50
When SalesProb = 5 then 60
When SalesProb = 6 then 70
When SalesProb = 7 then 80
When SalesProb = 8 then 90
When SalesProb = 9 then 100
ELSE null
END

To either this

CASE

When SalesProb = 0 then 10

When SalesProb = 1 then 20

When SalesProb = 2 then 30

When SalesProb = 3 then 40

When SalesProb = 4 then 50

When SalesProb = 5 then 60

When SalesProb = 6 then 70

When SalesProb = 7 then 80

When SalesProb = 8 then 90

When SalesProb = 9 then 100

ELSE null

END AS [SalesProb]

Or this

CASE SalesProb

When 0 then 10

When 1 then 20

When 2 then 30

When 3 then 40

When 4 then 50

When 5 then 60

When 6 then 70

When 7 then 80

When 8 then 90

When 9 then 100

ELSE null

END

My blog: http://jahaines.blogspot.com

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Simple CASE:

CASE SalesProb WHEN 0 THEN...

Searched CASE:

CASE WHEN SalesProb = 0 THEN...

Searched CASE is more expensive than simple CASE.

Either way you will need a column assignment:

,NewColumn = CASE...END

or

,CASE...END AS NewColumn

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Louise-604184 SSC-Addicted Points: 489 More actions · Answer 3

Thanks guys so much for your help. The nested sub-query with aliases did the trick in the end.

I feel pretty overwhelmed as I am trying to learn SQL Server, T-SQL and Reporting Services all at once in order to look after our companies sales tracking system. I haven't worked for a few years (family). Can anyone recommend good starter book on T-SQL as this is probably weakest bit at the moment.