如何在DB2选择用例中包含来自不同表的选择

Question

我在DB2服务器上运行了一个相当大的查询。在这个查询中，我有一个带有20个选项的case语句。在我尝试在每个when子句中添加一个子查询之前，这种情况非常有效。然后它给了我一个错误，我们不知道它发生在哪里。

Select SCDATA.SCCLNT.CCLNT,
  (Select SCDATA.SCCLNT.CNAME From SCDATA.SCCLNT
  Where SCDATA.SCCLNT.CLTGRP Like '231043' Fetch First
    1 Rows Only) As ClientName,
  CONCAT(TRIM(SCDATA.SCCLNT.CADD1), SCDATA.SCCLNT.CADD2) As Address1,
  CONCAT(TRIM(SCDATA.SCCLNT.CCITY), CONCAT(', ',
  CONCAT(TRIM(SCDATA.SCCLNT.CSTATE), CONCAT('  ',             
TRIM(SCDATA.SCCLNT.CZIP)))))
  As Address2,
  SCDATA.SCCLNT.CLTGRP As Group,
  CONCAT((Select     
VARCHAR_FORMAT(Min(TIMESTAMP_FORMAT(Cast(SCDATA.SCACCT.GPLDAT
    As Char(10)), 'YYYYMMDD')), 'MM/DD/YYYY') From SCDATA.SCACCT
  Where SCDATA.SCACCT.GPLDAT Between 20170401 and 20170501), CONCAT(' -     ',
  (Select VARCHAR_FORMAT(Max(TIMESTAMP_FORMAT(Cast(SCDATA.SCACCT.GPLDAT     As
    Char(10)), 'YYYYMMDD')), 'MM/DD/YYYY') From SCDATA.SCACCT
  Where SCDATA.SCACCT.GPLDAT Between 20170401 and 20170501))) As     dateRange,
  SCDATA.SCACCT.GCLNT,
  SCDATA.SCACCT.GPLACE,
  SCDATA.SCACCT.GPLDAT,
  SCDATA.SCACCT.GACCT# As GACCTnum,
  CONCAT(TRIM(SCDATA.SCACCT.GLAST), CONCAT(', ',     TRIM(SCDATA.SCACCT.GFIRST))) As
  Debtor,
  ( SCDATA.SCACCT.GPRI +  SCDATA.SCACCT.GINT) as Balance,
   SCDATA.SCACCT.Gdebt#,
CASE
   WHEN GDC20 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC20     FETCH TOP 1 ROWS)
   WHEN GDC19 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC19 FETCH TOP 1 ROWS)
   WHEN GDC18 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC18 FETCH TOP 1 ROWS)
   WHEN GDC17 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC17     FETCH TOP 1 ROWS)
   WHEN GDC16 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC16 FETCH TOP 1 ROWS)
   WHEN GDC15 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC15 FETCH TOP 1 ROWS)
   WHEN GDC14 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC14 FETCH TOP 1 ROWS)
   WHEN GDC13 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC13 FETCH TOP 1 ROWS)
   WHEN GDC12 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC12 FETCH TOP 1 ROWS)
   WHEN GDC11 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC11 FETCH TOP 1 ROWS)
   WHEN GDC10 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC10 FETCH TOP 1 ROWS)
   WHEN GDC09 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC09 FETCH TOP 1 ROWS)
   WHEN GDC08 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC08 FETCH TOP 1 ROWS) 
   WHEN GDC07 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC07 FETCH TOP 1 ROWS) 
   WHEN GDC06 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC06 FETCH TOP 1 ROWS) 
   WHEN GDC05 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC05 FETCH TOP 1 ROWS) 
   WHEN GDC04 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC04 FETCH TOP 1 ROWS) 
   WHEN GDC03 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC03 FETCH TOP 1 ROWS) 
   WHEN GDC02 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC02 FETCH TOP 1 ROWS) 
   WHEN GDC01 !='' THEN  (Select SDDESC from SC0016 where SDCODE=GDC01 FETCH TOP 1 ROWS) 
   ELSE 'xx'
END as Description


From SCDATA.SCCLNT
  inner Join SCDATA.SCACCT On SCDATA.SCACCT.GCLNT = SCDATA.SCCLNT.CCLNT     And
    SCDATA.SCACCT.GPLDAT Between 20170401 and 20170501
Where SCDATA.SCCLNT.CLTGRP Like '231043'
Group By SCDATA.SCCLNT.CCLNT,
  SCDATA.SCCLNT.CLTGRP,
  SCDATA.SCCLNT.CNAME,
  SCDATA.SCCLNT.CADD1,
  SCDATA.SCCLNT.CADD2,
  SCDATA.SCCLNT.CCITY,
  SCDATA.SCCLNT.CSTATE,
  SCDATA.SCCLNT.CZIP,
  SCDATA.SCACCT.GFIRST,
  SCDATA.SCACCT.GLAST,
  SCDATA.SCACCT.GCLNT,
  SCDATA.SCACCT.GPLACE,
  SCDATA.SCACCT.GPLDAT,
  SCDATA.SCACCT.GACCT#,
SCDATA.SCACCT.gpri,
SCDATA.SCACCT.GINT,
SCDATA.SCACCT.GDEBT#,
GDC20,
GDC19,
GDC18,
GDC17,
GDC16,
GDC15,
GDC14,
GDC13,
GDC12,
GDC11,
GDC10,
GDC09,
GDC08,
GDC07,
GDC06,
GDC05,
GDC04,
GDC03,
GDC02,
GDC01

这个查询的目的是获得最高级别的描述代码，以便在查询结果中显示，但是程序是构建的，以便随着时间的推移添加描述，所以我需要从20下降到1，才能找到最近使用的代码。代码是一个简单的2个字符组合，它对应于另一个表，在那里我需要提取信息。

问题是如何重新设计DB2中的case语句以从SC0016表中提取所需的单个值？

谢谢

Answer 1

最终，解决方案是将select大小写放在join语句中。因此，在select部分，我将列出我想要的字段，SDDESC，在联接中，我将在SDCODE = CASE上留下SC0016，当GDC20!='‘然后是GDC20 .结束

这将允许我根据第一个表中填充最多的列加入每一行。不太标准化，但我没有编写数据库:)这是可行的。