CASE WHEN Statement when creating a VIEW

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » CASE WHEN Statement when creating a VIEW

CASE WHEN Statement when creating a VIEW

Rate Topic

Display Mode

Topic Options

Author

Message

SteveEClarke

Posted Monday, December 03, 2012 2:55 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 5:22 AM
Points: 108, Visits: 292

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 ?

Post #1391820

Eugene Elutin

Posted Monday, December 03, 2012 3:35 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:59 AM
Points: 2,525, Visits: 4,324

... 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!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1391831

SteveEClarke

Posted Monday, December 03, 2012 3:39 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 5:22 AM
Points: 108, Visits: 292

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.

Post #1391832