"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, open_balance, end_balance 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.