sql server - using CASE WHEN in SQL Statement

Question

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  

Answer 1

Why not cast the number columns to varchar columns?

If you're using SQL SERVER you can do that like so:

CONVERT(VARCHAR,secorg.org_id) = CONVERT(VARCHAR,progmap.org_id)

You'll have to do an outer join for instances when the column that is both 'ALL' and numbers is 'All' as it won't be able to inner join to the other table.

For the quick fix based on your code above you can just change the second WHEN clause to look like so (again assuming you're using MS SQL SERVER):

WHEN CONVERT(VARCHAR,secorg.org_id) = CONVERT(VARCHAR,progmap.org_id) THEN secorg.org_name

Try this as your query:

SELECT DISTINCT 
    program_id, 
    prog_name,
    CASE Eitc_Active_Switch
        WHEN '1' THEN 'ON'
        ELSE 'OFF'
    END AS Prog_Status,
    progmap.client_id,
    ISNULL(secorg.org_name,'ALL') AS org_name,
    CASE prog.has_post_calc_screen
        WHEN '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
LEFT OUTER JOIN sec_organization secorg (nolock) ON CONVERT(VARCHAR,secorg.org_id) = CONVERT(VARCHAR,progmap.org_id)