Write SQL query

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

No comments:

Post a Comment