CASE WHEN Statement when creating a VIEW

Question

Post reply

CASE WHEN Statement when creating a VIEW

SteveEClarke

SSCertifiable

Points: 5161
More actions
December 3, 2012 at 2:55 am

#267677

Hi Guys,
I am having a few issues with some code and wanted some guidance ;
I am creating a view based on a table, and need to create a "Identify" column, based on the data in the table ;
Simple - 3 columns
StockCode, MorB, Warehouse
StockA,M,AA
StockB,B,AA
StockC,B,BB
StockD,B,CC
StockE,M,BB
My Logic;
Select
StockCode, MoRB, ,Warehouse,
CASE
WHEN Warehouse = 'CC' then 'Cars'
WHEN MorB = 'B' then 'Bought'
WHEN Warehouse = 'AA' then 'Available'
ELSE 'xxx'
END
as StockCategory
Reseult:
I would hope to see the following;
StockD,B,CC,'Cars'
StockB,B,AA,'Bought'
StockA,M,AA,'Available'
StockC,B,BB,'xxx'
StockE,M,BB,'xxx'
My question is; Do I understand the CASE logic correctly and does it do one condition and then drop to the CASE...END statement after satisfying one of the conditions? or does it do EVERY CASE WHEN check and hold the last one as the description ?
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

... does it do one condition and then drop to the CASE...END statement after satisfying one of the conditions?

That is correct.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 2

Eugene Elutin (12/3/2012)
... does it do one condition and then drop to the CASE...END statement after satisfying one of the conditions?
That is correct.

Sorry;

My question is;

Do I understand the CASE logic correctly and does it do one condition and then drop to the CASE...END statement after satisfying one of the conditions?

OR

Does it do EVERY CASE WHEN check and hold the last one as the description ?

So if I put an initial check in saying ... CASE StockCode like 'Stock%' Then 'Yippee'

ALL Records would fall into this and no other categories would be highlighted.

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

It hits the first true condition then terminates the case statement.

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 4

anthony.green (12/3/2012)
It hits the first true condition then terminates the case statement.

Thank you - I did feel this was the case but the data does not reflect the logic!?

Will now dig deeper.

Thank you

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.