有选择地在结果中显示文本或数字

Question

我像下面这样创建sql，当我尝试执行sql时，"MONTH"列变为空。它应该显示 CPPERI = 1 的 JAN，但如果我将 JAN 更改为数字，那么"MONTH"列将显示数字请帮助解决这个问题。谢谢

select EGAIT1 as "ACCOUNT NO" ,EATX15 as "DESC",CPYEA4 as "YEAR",
(case when CPPERI = 1 then  'JAN' else 0 end) as "MONTH",
sum(EGCUAM)as "AMT",EGAIT2 as "DEPARTMENT" from FGLEDG
INNER JOIN FCHACC on (EGCONO=EACONO and EGDIVI=EADIVI and EGAIT1 = EAAITM)
INNER JOIN CSYPER on (EGCONO=CPCONO and CPDIVI='TOH' and EGACDT between CPFDAT and CPTDAT)
where EGACDT between '20170101' and '20170131'
and EGAIT1 in ('56010','56020','56030','56040','56050')
and EGAIT2 in  ('AH03' ,'AH04' )
group by EGAIT1,EATX15,CPYEA4,CPPERI,CPTX15,EGAIT2
order by EGAIT1

Answer 1

case 语句中的两个结果都必须返回相同的数据类型。在您的情况下，"when"部分返回 varchar，否则返回部分整数。尝试这样的事情

 case when CPPERI = 1 then  'JAN' else '0' end