当不满足条件时，Postgresql case语句返回空行

Question

我在query中有以下语句

CASE 
  WHEN "PI".statusid_dec = 'Entered' 
   and "PI".pagename = 'Procedural Information' 
   and "PI".pishact IS NULL 
   then 'ACT (Activated Clotting time)' 
END

问题是，如果有"PI".pagename is NOT 'Procedural Information'的记录，我会在这些情况下得到一个额外的(空白)行。如何修改此查询，使其在每次'case when'语句完全匹配时只获得一行？

完成查询：

SELECT

DISTINCT "DAT_SUB".SUBNUM as "Subject ID",

CONCAT_WS(',',

CASE WHEN "DM".statusid_dec = 'Entered' and "DM".sex IS NULL then 'Gender' 
END,
CASE WHEN "DM".statusid_dec = 'Entered' and "DM".brthyr IS NULL then 'Year 
of 
Birth' END,
CASE WHEN "DM".statusid_dec = 'Entered' and "DM".icdat IS NULL then 
'Informed 
consent date' END) as "Demographics",

CASE WHEN "PI".statusid_dec = 'Entered' and "PI".pagename = 'Procedural 
Information' and "PI".pishact IS NULL then 'ACT (Activated Clotting time)' 
END 
as "Procedure"

FROM "Disco_Radial"."DAT_SUB"
//These are all the tables I want to add similar case-statements for. They 
are currently not being used yet, because I wanted to solve this issue 
above first. 

LEFT JOIN "Disco_Radial"."ACM" ON "Disco_Radial"."ACM".SUBNUM = 
"DAT_SUB".SUBNUM
LEFT JOIN "Disco_Radial"."AE" ON "Disco_Radial"."AE".SUBNUM = 
"DAT_SUB".SUBNUM
LEFT JOIN "Disco_Radial"."CH" ON "Disco_Radial"."CH".SUBNUM = 
"DAT_SUB".SUBNUM
LEFT JOIN "Disco_Radial"."CL" ON "Disco_Radial"."CL".SUBNUM = 
"DAT_SUB".SUBNUM
LEFT JOIN "Disco_Radial"."CRF" ON "Disco_Radial"."CRF".SUBNUM = 
"DAT_SUB".SUBNUM
LEFT JOIN "Disco_Radial"."DIS" ON "Disco_Radial"."DIS".SUBNUM = 
"DAT_SUB".SUBNUM
LEFT JOIN "Disco_Radial"."DM" ON "Disco_Radial"."DM".SUBNUM = 
"DAT_SUB".SUBNUM
LEFT JOIN "Disco_Radial"."IE" ON "Disco_Radial"."IE".SUBNUM = 
"DAT_SUB".SUBNUM
LEFT JOIN "Disco_Radial"."LB" ON "Disco_Radial"."LB".SUBNUM = 
"DAT_SUB".SUBNUM
LEFT JOIN "Disco_Radial"."PA" ON "Disco_Radial"."PA".SUBNUM = 
"DAT_SUB".SUBNUM
LEFT JOIN "Disco_Radial"."PE" ON "Disco_Radial"."PE".SUBNUM = 
"DAT_SUB".SUBNUM
LEFT JOIN "Disco_Radial"."PI" ON "Disco_Radial"."PI".SUBNUM = 
"DAT_SUB".SUBNUM
LEFT JOIN "Disco_Radial"."SE" ON "Disco_Radial"."SE".SUBNUM = 
"DAT_SUB".SUBNUM
LEFT JOIN "Disco_Radial"."SS" ON "Disco_Radial"."SS".SUBNUM = 
"DAT_SUB".SUBNUM
LEFT JOIN "Disco_Radial"."VS" ON "Disco_Radial"."VS".SUBNUM = 
"DAT_SUB".SUBNUM
LEFT JOIN "Disco_Radial"."DAT_RAND" ON "Disco_Radial"."DAT_RAND".SUBNUM = 
"DAT_SUB".SUBNUM
LEFT JOIN "Disco_Radial"."DAT_PAGS" ON "Disco_Radial"."DAT_PAGS".SUBNUM = 
"DAT_SUB".SUBNUM

ORDER BY "DAT_SUB".subnum;

输出如下所示：

"DR-05-001";"";"ACT (激活凝血时间)“”DR-05-001“；”“；"DR-05-002";"";"ACT (激活凝血时间)”"DR-05-003";"";"ACT (激活凝血时间)“"DR-05-004";"";"ACT (激活凝血时间)”"DR-05-005";"";"ACT (激活凝血时间)“"DR-05-006";""；"ACT (激活的凝血时间)“

..。

这个问题是针对DR-05-001这样的记录，它在"PI“表中有行没有页面名”Procedural“。

Answer 1

case表达式只指定每行要返回的值。如果您希望排除某些行，则需要添加一个包含该逻辑的where子句。

Answer 2

将WHERE "PI".pagename = 'Procedural Information'添加到查询中，您可以将其从CASE表达式中删除：

SELECT
  CASE 
    WHEN "PI".statusid_dec = 'Entered' and "PI".pishact IS NULL 
    THEN 'ACT (Activated Clotting time)' 
  END
FROM "PI"
WHERE "PI".pagename = 'Procedural Information'

请注意，如果条件：

"PI".statusid_dec = 'Entered' and "PI".pishact IS NULL

对于任何行，如果不为TRUE，则在结果中仍然会得到空行。