"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.
