Question : In the domain table we have status as a numeric value from 01 to 04 and we have text definition of these values in the design document.
Write SQL query to see the result as a text definitions that is corresponded to these values. (DB2)
Answer: select TB1.member_id, TB1.bu_id, TB1.program, TB2.num,
case TB1.status
when '01' then 'Auto renew'
when '02' then 'Expired'
when '03' then 'Sold'
when '04' then 'Terminated'
else TB_name.status
end
from DB_name.TB_name1 TB1,
DB_name.TB_name2 TB2
where
TB1.program in ('com', 'org')
and TB1.member_role = '100'
order by TB1.member_id
fetch first 30 rows only