Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CASE WHEN Statement when creating a VIEW Expand / Collapse
Author
Message
Posted Monday, December 3, 2012 2:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 17, 2014 3:53 AM
Points: 177, Visits: 419
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.
Post #1391820
Posted Monday, December 3, 2012 3:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 2,873, Visits: 5,189
... 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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1391831
Posted Monday, December 3, 2012 3:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 17, 2014 3:53 AM
Points: 177, Visits: 419
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.
Post #1391832
Posted Monday, December 3, 2012 3:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:10 AM
Points: 5,221, Visits: 5,119
It hits the first true condition then terminates the case statement.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1391833
Posted Monday, December 3, 2012 3:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 17, 2014 3:53 AM
Points: 177, Visits: 419
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.
Post #1391834
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse