I have to display information by comparing data in two ID columns, one column has 'ALL' and numbers as ID's while the other has only numbers in it as ID's. My problem is, I cannot compare character and number columns with a number column. So I am using CASE WHEN.
If the value is 'ALL' then display 'ALL' in the output, else display name for the matching records.
Here is the code:
CASE
WHEN secorg.org_id = 'ALL' THEN 'ALL'
WHEN secorg.org_id = progmap.org_id THEN secorg.org_name
END AS org_name,
the condition is this: 'secorg.org_id = progmap.org_id' which is based on the id and I have to display secorg.org_name if the id's are same.
Here is the entire query:
SELECT distinct program_id,
prog_name,
case
when Eitc_Active_Switch = '1' then 'ON'
when Eitc_Active_Switch = '0'then 'OFF'
End as Prog_Status,
progmap.client_id,
case
when secorg.org_id = 'ALL' then 'ALL'
--when secorg.org_id = progmap.org_id then secorg.org_name
else secorg.org_name
end as org_name,
case
when prog.has_post_calc_screen = 'True' then 'True'
Else 'False'
End as Referal_ID,
case
when progmap.program_ID IN ( 'AMC1931', 'AMCABD', 'AMCMNMI',
'AMC' )
And sec.calwinexists_ind = '1' then 'Yes'
when progmap.program_ID IN ( 'AMC1931', 'AMCABD', 'AMCMNMI',
'AMC' )
And sec.calwinexists_ind = '0'then 'No'
when progmap.program_ID NOT IN (
'AMC1931', 'AMCABD', 'AMCMNMI', 'AMC' ) then
'N/A'
End as calwin_interface,
sec.Client_name
FROM ref_programs prog (nolock)
LEFT OUTER JOIN ref_county_program_map progmap (nolock)
ON progmap.program_id = prog.prog_id
AND progmap.CLIENT_ID = prog.CLIENT_ID
INNER join sec_clients sec (nolock)
on sec.client_id = progmap.Client_id
Inner join sec_organization secorg (nolock)
on secorg.org_id = progmap.org_id
See Question&Answers more detail:os