在SQL语句中使用CASE WHEN [英] using CASE WHEN in SQL Statement

问题描述

我必须通过比较两个ID列中的数据来显示信息，一列具有"ALL"和数字作为ID，而另一列中仅具有数字作为ID.我的问题是，我无法将字符和数字列与数字列进行比较.所以我在使用CASE WHEN.

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.

如果值为'ALL'，则在输出中显示'ALL'，否则显示匹配记录的名称.

If the value is 'ALL' then display 'ALL' in the output, else display name for the matching records.

这是代码:

CASE
  WHEN secorg.org_id = 'ALL' THEN 'ALL'
  WHEN secorg.org_id = progmap.org_id THEN secorg.org_name
END AS org_name,

条件是这样的:'secorg.org_id = progmap.org_id'是基于ID的，如果ID相同，我必须显示secorg.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.

这是整个查询:

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  

推荐答案

case语句很好，它的字段别名应该不好

The case statement is fine its the field alias thats bad it shoud be

     END As org_name

像secorg.org_name这样的多部分别名不起作用

A multipart alias like secorg.org_name won't work

这篇关于在SQL语句中使用CASE WHEN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！