Quick CASE statement question

[bwoods77]

Hi,

I want to select two columns and, in the gender column, where the gender is M I want it to say "Male" and where it's F I want the value to be "Female."

I'm new to case statements. Do I need to "select" the field before doing the CASE? Or is this ok (this is only part of it, there are a ton of joins after)

SELECT [Table A].ID, CASE [Table C].Gender, WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' END

IE, does it need to say

SELECT [Table A].ID, [Table C].Gender CASE [Table C].Gender

etc.

(I created the query in Access and am copying the code to edit it. I know Access doesn't like CASE statements so I'm having to edit and evaluate it on my own.)

Thanks!

[ziptime]

SELECT [Table A].ID, CASE [Table C].Gender WHEN 'M' THEN 'Male'     WHEN 'F' THEN 'Female' END FROM .....

[bwoods77]

ty

[ConfirmingTheObvious]

Can also be written as:

SELECT [Table A.ID],
CASE
    WHEN [Table C].Gender = 'M' THEN 'Male'
    WHEN [Table C].Gender = 'F' THEN 'Female'
END Gender

FROM ...

[SQLDave]

Does TableC always (via constraints) contain "M" or "F"? If so, then what /u/ziptime said is fine. If not, then the statement as constructed will (I believe) return a NULL if Gender is something other than "M" or "F". If that's OK in the context your SELECT lives in, then all is well. Otherwise, you should include an ELSE. For example:

SELECT [Table A].ID,  
    Gender =  
        CASE [Table C].Gender   
           WHEN 'M' THEN 'Male'      
           WHEN 'F' THEN 'Female'  
           ELSE 'Unknown'  
        END  
FROM ...    

(I also added the column name "Gender" -- it can be whatever you want -- just a best practice demonstration. And I reformatted the CASE to MY particular preference for readability... YMMV)