我有以下场景，我必须更正/合并不正确的历史处理记录：
Acct_Id status_cd Start_Dttm End_Dttm ROLE_CD
1,108,389,774 11 6/10/2009 18:03 7/18/2018 4:11 6
1,108,389,774 11 7/18/2018 4:11 8/8/2018 7:18 6
1,108,389,774 8 8/8/2018 7:18 ? 6
我必须将前两个记录合并为历史错误的记录。我正在使用以下查询。
SELECT acct_id,acct_status_cd,acct_status_start_dttm ,acct_status_end_dttm ,acct_status_role_cd,run_id, data_source_type_cd,
BEGIN(PD) AS acct_status_start_dttm , NULLIF(END(PD), TIMESTAMP '9999-12-31 00:00:00') AS acct_status_end_dttm
FROM
(
SELECT NORMALIZE --
acct_id,trim(acct_status_cd) as acct_status_cd ,acct_status_start_dttm ,acct_status_end_dttm ,acct_status_role_cd,run_id, data_source_type_cd,
PERIOD(acct_status_start_dttm,COALESCE(acct_status_end_dttm, TIMESTAMP '9999-12-31 00:00:00')) AS PD
FROM dp_dqmt.acct_stat_hh
where acct_status_start_dttm < acct_status_end_dttm
) AS DT
谁能告诉这里有什么问题吗？
您需要删除日期列，因为它们不相同，因此normalize不会合并两行：
SELECT acct_id,acct_status_cd
-- acct_status_start_dttm ,acct_status_end_dttm ,
,acct_status_role_cd,run_id, data_source_type_cd,
Begin(PD) AS acct_status_start_dttm , NullIf(End(PD), TIMESTAMP '9999-12-31 00:00:00') AS acct_status_end_dttm
FROM
(
SELECT NORMALIZE --
acct_id,Trim(acct_status_cd) AS acct_status_cd ,
-- acct_status_start_dttm ,acct_status_end_dttm ,
acct_status_role_cd,run_id, data_source_type_cd,
PERIOD(acct_status_start_dttm,Coalesce(acct_status_end_dttm, TIMESTAMP '9999-12-31 00:00:00')) AS PD
FROM dp_dqmt.acct_stat_hh
WHERE acct_status_start_dttm < acct_status_end_dttm
) AS DT
该where也将与一个NULL acct_status_end_dttm删除最后一行，除非你将其更改为
WHERE acct_status_start_dttm < Coalesce(acct_status_end_dttm, TIMESTAMP '9999-12-31 00:00:00')
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句