引用要在CASE子句中使用的相同SQL列

Question

我需要一些使用Qrkstation z/OSV11.1Fix Pack3在DB2中进行SQL查询的帮助。

我正在从不同的表中查询几个字段。我也展示了一些时间戳。我需要一个使用其中一个时间戳的CASE子句。

其思想是，其中一个时间戳可以有数据，也可以没有数据(null)，因为我对该表使用了左连接(if value，则将其带入，如果不是，则为空)，因此CASE子句将需要检查该列/字段，如果它有数据(IS not NULL)，则将value放入“Delivered”。如下图所示，这种逻辑对我不起作用。

select distinct
    z.po_id, 
    max(CASE 
           WHEN dcus.ship_evnt_tms IS NOT NULL THEN 'Delivered'
           WHEN days(CURRENT_timestamp) - days(Z.CAD_TMS) <= 0 THEN 'On time CAD'
           WHEN days(CURRENT_timestamp) - days(Z.CAD_TMS) between 1 and 2 THEN 'Less than 3 days after CAD'
           WHEN days(CURRENT_timestamp) - days(Z.CAD_TMS) between 3 and 5 THEN 'Between 3 & 5 days after CAD'
           WHEN days(CURRENT_timestamp) - days(Z.CAD_TMS) between 6 and 10 THEN 'Between 6 & 10 days after CAD'
           WHEN days(CURRENT_timestamp) - days(Z.CAD_TMS) >= 10 THEN 'More than 10 days after CAD'
           ELSE 'Non physical' 
       END) AS Aging,
    max(z.cad_tms) as cad_tms, 
    max(dcus.ship_evnt_tms) as DELV_DATE_CUS
from
    SCHEMA.TABLE z
left join 
    SCHEMA.TABLE1 A ON z.po_id = a.po_id
left join
    SCHEMA.TABLE2 scus ON (A.SHIP_ID = scus.SHIP_ID AND A.SHIP_TO_LOC_CODE = scus.SHIP_TO_LOC_CODE and scus.loc_type = 'CUS')
left join 
    SCHEMA.TABLE3 dcus ON (scus.SHIP_ID = dcus.SHIP_ID_856 AND scus.SHIP_TO_LOC_CODE = dcus.SHIP_TO_LOC_CD856 
AND dcus.SHIP_EVNT_CD = 'D')
left join
    SCHEMA.TABLE2 scdc ON (A.SHIP_ID = scdc.SHIP_ID AND A.SHIP_TO_LOC_CODE = scdc.SHIP_TO_LOC_CODE and scdc.loc_type = 'CDC')
left join 
    SCHEMA.TABLE3 dcdc ON (scdc.SHIP_ID = dcdc.SHIP_ID_856 AND scdc.SHIP_TO_LOC_CODE = dcdc.SHIP_TO_LOC_CD856 
AND dcdc.SHIP_EVNT_CD = 'D')

​

​

如图所示，在最后一列具有数据am的前几行应该显示为基于CASE子句逻辑(至少据我所知)。此外，最后几条记录显示了基于其他WHEN语句的正确计算和结果。

我尝试删除表别名(dcus)以“引用”列值本身，但它给出了一个SQL错误(不明确的引用)

​

​

如果任何人知道不同的方法或在条款中看到某种错误，请让我知道。

谢谢！！

Answer 1

我认为主要问题是您为每个id选择了case语句返回的最大值，而不是为最大日期选择CAS语句的值。因此，这些值的顺序是(按时间CAD、非物理、超过10天...、少于3天...、交付、介于6...、介于3)。因此，如果所有这些id都有一条ship_evnt_tms为空的记录，那么从这种情况中得到的最大值将是More than 10 days...。同样奇怪的是，您将distinct与聚合一起使用。取而代之的是，你可以在po_id上分组。也许可以试试：

select
z.po_id, 
CASE 
  WHEN max(dcus.ship_evnt_tms) is not null then 'Delivered'
  WHEN days(CURRENT_timestamp) - days(max(Z.CAD_TMS)) <= 0 then 'On time CAD'
  WHEN days(CURRENT_timestamp) - days(max(Z.CAD_TMS)) between 1 and 2 then 'Less than 3 days after CAD'
  WHEN days(CURRENT_timestamp) - days(max(Z.CAD_TMS)) between 3 and 5 then 'Between 3 & 5 days after CAD'
  WHEN days(CURRENT_timestamp) - days(max(Z.CAD_TMS)) between 6 and 10 then 'Between 6 & 10 days after CAD'
  WHEN days(CURRENT_timestamp) - days(max(Z.CAD_TMS)) >= 10 then 'More than 10 days after CAD'
  ELSE 'Non physical' 
END AS Aging,

max(z.cad_tms) as cad_tms, 
max(dcus.ship_evnt_tms) as DELV_DATE_CUS

from SCHEMA.TABLE z

left join SCHEMA.TABLE1 A ON z.po_id = a.po_id

left JOIN SCHEMA.TABLE2 scus ON (A.SHIP_ID = scus.SHIP_ID AND A.SHIP_TO_LOC_CODE = scus.SHIP_TO_LOC_CODE and scus.loc_type = 'CUS')

left join SCHEMA.TABLE3 dcus ON (scus.SHIP_ID = dcus.SHIP_ID_856 AND scus.SHIP_TO_LOC_CODE = dcus.SHIP_TO_LOC_CD856 
AND dcus.SHIP_EVNT_CD = 'D')

left JOIN SCHEMA.TABLE2 scdc ON (A.SHIP_ID = scdc.SHIP_ID AND A.SHIP_TO_LOC_CODE = scdc.SHIP_TO_LOC_CODE and scdc.loc_type = 'CDC')

left join SCHEMA.TABLE3 dcdc ON (scdc.SHIP_ID = dcdc.SHIP_ID_856 AND scdc.SHIP_TO_LOC_CODE = dcdc.SHIP_TO_LOC_CD856 
AND dcdc.SHIP_EVNT_CD = 'D')

group by z.po_id