Results 1 to 3 of 3
  1. #1
    Join Date
    May 2006
    Posts
    38

    Question Unanswered: Case statement in SQL 2005

    I came across a case statement with the END having "END = value"

    I parsed the query and it came up fine .. just wanted to know what will be the output ..




    SELECT au_fname, au_lname,
    CASE state
    WHEN 'CA' THEN 1
    WHEN 'KS' THEN 10
    WHEN 'TN' THEN 100
    ELSE 1000
    END = 99


    What is Value of state , assuming a person's name is

    Scenario 1

    au_fname = Ranjit
    au_lname = Hans
    state = AZ

    Scenario 1

    au_fname = Brett
    au_lname = Crowley
    state = TN


    PS: My optimizer is having strange issues with the values and hence wanted to confirm

    Thanks as always!

    Warm Regards,
    RanjitSHans

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Just out of curiosity, why don't you simply run those statements on SQL Server?

    I think "END = 99" is an artefact left from the development of the query. I would change it to something like "END AS StateNr". I have noticed SQL Server sometimes misses those quirks, doesn't give any warning or error message, but gives wrong results afterwards. Perhaps that is what you are experiencing.

    What do you think those Scenarios will give as result?
    Last edited by Wim; 10-24-09 at 18:39.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Aug 2009
    Posts
    262
    SELECT au_fname, au_lname,
    CASE state
    WHEN 'CA' THEN 1
    WHEN 'KS' THEN 10
    WHEN 'TN' THEN 100
    ELSE 1000
    END = 99


    here
    SELECT au_fname, au_lname,
    CASE state
    WHEN 'CA' THEN 1
    WHEN 'KS' THEN 10
    WHEN 'TN' THEN 100
    " ELSE 1000 " = ( IF NULL then 1000)
    END = 99


    so you can change it to

    SELECT au_fname, au_lname,
    CASE state
    WHEN 'CA' THEN 1
    WHEN 'KS' THEN 10
    WHEN 'TN' THEN 100
    when IS NULL then 1000
    else 99
    end

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •