Menu Bar

Friday, January 6, 2012

CASE in point

"They have a cave troll!" - Boromir, The Fellowship of the Ring
I recently needed to write a query where based upon the value of a lookup field, I needed to find the description of a code.

I can never remember the exact syntax of a case statement, so I thought I would throw a post up here with what I did so that I could refer to it later.

select account_code,
       case when account_ind = '1' then  
                             (select cost_center_description 
                              from cost_centers 
                              where cost_center_code = account_code)
            when account_ind = '2' then  
                             (select exp_element_description 
                              from exp_elements 
                              where exp_element_code = account_code)
            when account_ind = '3' then  
                             (select gl_description 
                              from gl_codes
                              where gl_code = account_code)
            else 'Unknown'
       end as account_desc,
from account_codes

Nothing ground breaking in this one, but I know that I'll refer to it regularly because nitty gritty things like syntax is something that I often need to look up.