Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2007
    Posts
    2

    Unanswered: Case expression help

    I can't seem to figure out why this case expression isn't working -

    'BR' =
    Case
    when
    tblEMSO_Temp_POS_SUM.Brand = 'H' then 'HES'
    tblEMSO_Temp_POS_SUM.Brand = 'F' then 'FA'
    tblEMSO_Temp_POS_SUM.Brand = 'S' then 'SEC'
    tblEMSO_Temp_POS_SUM.Brand = 'A' then 'AR'

    End

    I'm getting the msg 102, incorrect syntax on this line (tblEMSO_Temp_POS_SUM.Brand = 'H' then 'HES'). But if I remove the lines below it, the query works fine.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    You are close, jbphoenix. You just need a WHEN in from of each check:
    Code:
    'BR' =
    Case
         WHEN tblEMSO_Temp_POS_SUM.Brand = 'H' then 'HES'
         WHEN tblEMSO_Temp_POS_SUM.Brand = 'F' then 'FA'
         WHEN tblEMSO_Temp_POS_SUM.Brand = 'S' then 'SEC'
         WHEN tblEMSO_Temp_POS_SUM.Brand = 'A' then 'AR'
    
    End

  3. #3
    Join Date
    Jul 2007
    Location
    Belgium
    Posts
    15
    Quote Originally Posted by Stealth_DBA
    You are close, jbphoenix. You just need a WHEN in from of each check:
    Code:
    'BR' =
    Case
         WHEN tblEMSO_Temp_POS_SUM.Brand = 'H' then 'HES'
         WHEN tblEMSO_Temp_POS_SUM.Brand = 'F' then 'FA'
         WHEN tblEMSO_Temp_POS_SUM.Brand = 'S' then 'SEC'
         WHEN tblEMSO_Temp_POS_SUM.Brand = 'A' then 'AR'
    
    End
    And I would add an ELSE unless tblEMSO_Temp_POS_SUM.Brand did not allow nulls and the only valid values were H, F, S, and A.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    CASE tblEMSO_Temp_POS_SUM.Brand 
         WHEN 'H' THEN 'HES'
         WHEN 'F' THEN 'FA'
         WHEN 'S' THEN 'SEC'
         WHEN 'A' THEN 'AR'
                  ELSE '??' END   AS BR
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2007
    Posts
    2

    Thanks

    Thanks all - sometime I get in a hurry and miss the obvious.

Posting Permissions

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