CASE WHEN Statement when creating a VIEW

  • 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.

  • ... 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]

  • 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.

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

  • 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.

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

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