在 group by 子句中不允许使用 Teradata Orderer 分析函数 [英] Teradata Orderer Analytical Function not allowed in group by clause
本文介绍了在 group by 子句中不允许使用 Teradata Orderer 分析函数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我收到错误
在 group by 子句中不允许使用 Teradata Orderer 分析函数
Teradata Orderer Analytical Function not allowed in group by clause
当我运行这个查询时:
SELECT
CC.CASE_ID as CASE_ID,
FIRST_VALUE(CC.CASE_OWN_NM) OVER(PARTITION BY CC.CASE_ID) as FST_AGNT_CASE_OWN_NM,
FIRST_VALUE(CC.LSTMOD_BY_AGNT_PRFL_NM) OVER(PARTITION BY CC.CASE_ID) as FST_AGNT_PRFL_NM,
LAST_VALUE(CC.CASE_OWN_NM) OVER(PARTITION BY CC.CASE_ID) as LST_AGNT_CASE_OWN_NM,
LAST_VALUE(CC.LSTMOD_BY_AGNT_PRFL_NM) OVER(PARTITION BY CC.CASE_ID) as LST_AGNT_PRFL_NM,
case when CC.CASE_OWN_NM is not null then MIN(CC.REC_DTTM_PST) end as FST_AGNT_EDIT_DTTM,
case when CC.CASE_OWN_NM is not null then MAX(CC.REC_DTTM_PST) end as LST_AGNT_EDIT_DTTM,
case when CC.CASE_STS_CD='Open' then MIN(CC.REC_DTTM_PST) end as CASE_OPEN_DTTM,
case when CC.CASE_STS_CD in ('Closed', 'Auto Closed') then MIN(CC.REC_DTTM_PST) end as CASE_CLSE_OR_AUTO_CLSE_DTTM,
count(distinct CC.CASE_OWN_NM) as CASE_OWN_CHGS_IN_NUM,
LAST_VALUE(CC.ESCL_RSN_TXT) OVER(PARTITION BY CC.CASE_ID) as ESCL_RSN_TXT,
LAST_VALUE(CC.ESCL_DTLS_TXT) OVER(PARTITION BY CC.CASE_ID) as ESCL_DTLS_TXT
FROM
EDW_KATAMARI_T.CNTCT_CASE CC
INNER JOIN
EDW_KATAMARI_T.CNTCT_CASE_EXTN CCE ON CC.CNTCT_CASE_APND_KEY = CCE.CNTCT_CASE_APND_KEY
INNER JOIN
EDW_STAGE_COMN_SRC.STG_CNTCT_CASE_DELTA DELTA on CC.CASE_ID = DELTA.CASE_ID
group by
1,2,3,4,5
sample 10
<小时>
我尝试了 anwaar_hell 的答案 并稍微修改了几列,但我仍然得到错误:
I tried the answer by anwaar_hell and modified it a bit with a few more columns, but I'm still getting an error:
子查询中不允许使用有序分析函数
ordered analytical function not allowed in subqueries
查询:
SELECT
distinct CC.CASE_ID as CASE_ID,
CCC.FST_AGNT_CASE_OWN_NM,
CCC.FST_AGNT_PRFL_NM,
CCC.LST_AGNT_CASE_OWN_NM,
CCC.LST_AGNT_PRFL_NM,
CCC.FST_CHNL_NM,
CCC.LST_CHNL_NM,
CCC.LST_VEND_NM,
case when MAX(CC.CASE_TFR_TO_L2)='1' then 'Yes' else 'No' end as ESCL_FL,
case when CC.LSTMOD_BY_AGNT_ROLE_NM='L1' then count(distinct CC.LSTMOD_BY_AGNT_ROLE_NM) end as XFER_BTWN_L1_NUM,
MAX(CC.CASE_SAVED_ORD_NUM) as SAVES_OR_MODS_ON_CASE_NUM,
MIN(CC.REC_DTTM_PST) as FST_QUE_TIME_IN_SECS2,
case when CC.RMTE_ASST_USED_IN>=1 then 'Yes' else 'No' end as RMTE_SESS_FL,
case when CC.OUTB_CALL_TYPE_CD='Outbound' then 1 else 0 end as IS_OUTB_CALL_TYPE_IN,
case when CC.L2CALL_BK_SCEHDULED_PST_DT is NOT NULL then 'Yes' else 'No' end as L2_OUTB_CALL_SCHD_FL,
MAX(CC.L2CALL_BK_SCEHDULED_PST_DT) as L2_CLBCK_SCEHDULED_DTTM,
CC.PU_DTTM as LMI_PU_DTTM,
CC.CLS_DTTM as LMI_CLS_DTTM,
( select
FIRST_VALUE(CC.CASE_OWN_NM) OVER(PARTITION BY CC.CASE_ID) as FST_AGNT_CASE_OWN_NM,
FIRST_VALUE(CC.LSTMOD_BY_AGNT_PRFL_NM) OVER(PARTITION BY CC.CASE_ID) as FST_AGNT_PRFL_NM,
LAST_VALUE(CC.CASE_OWN_NM) OVER(PARTITION BY CC.CASE_ID) as LST_AGNT_CASE_OWN_NM,
LAST_VALUE(CC.LSTMOD_BY_AGNT_PRFL_NM) OVER(PARTITION BY CC.CASE_ID) as LST_AGNT_PRFL_NM,
FIRST_VALUE(CC.CHNL_NM) OVER(PARTITION BY CC.CASE_ID ) as FST_CHNL_NM,
LAST_VALUE(CC.CHNL_NM) OVER(PARTITION BY CC.CASE_ID) as LST_CHNL_NM,
LAST_VALUE(CCE.VEND_NM) OVER(PARTITION BY CC.CASE_ID) as LST_VEND_NM
FROM
EDW_KATAMARI_T.CNTCT_CASE CC
INNER JOIN
EDW_KATAMARI_T.CNTCT_CASE_EXTN CCE ON CC.CNTCT_CASE_APND_KEY = CCE.CNTCT_CASE_APND_KEY
INNER JOIN
EDW_STAGE_COMN_SRC.STG_CNTCT_CASE_DELTA DELTA on CC.CASE_ID = DELTA.CASE_ID
where
CC.CASE_ID='23268760'
)
group by
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
推荐答案
需要计算导出表中的第一个和最后一个值,然后分组
You need to calculate the first and last values in derive table and then group by
SELECT CC.CASE_ID as CASE_ID, CC.FST_AGNT_CASE_OWN_NM, CC.FST_AGNT_PRFL_NM,
CC.LST_AGNT_CASE_OWN_NM, CC.LST_AGNT_PRFL_NM,CC.ESCL_RSN_TXT,CC.ESCL_DTLS_TXT,
case
when CC.CASE_OWN_NM is not null then MIN(CC.REC_DTTM_PST)
end as FST_AGNT_EDIT_DTTM,
case
when CC.CASE_OWN_NM is not null then MAX(CC.REC_DTTM_PST)
end as LST_AGNT_EDIT_DTTM,
case
when CC.CASE_STS_CD='Open' then MIN(CC.REC_DTTM_PST)
end as CASE_OPEN_DTTM,
case
when CC.CASE_STS_CD in ( 'Closed', 'Auto Closed') then MIN(CC.REC_DTTM_PST)
end as CASE_CLSE_OR_AUTO_CLSE_DTTM, count( distinct CC.CASE_OWN_NM) as CASE_OWN_CHGS_IN_NUM
FROM (select FIRST_VALUE(CC.CASE_OWN_NM) OVER(PARTITION BY CC.CASE_ID )as FST_AGNT_CASE_OWN_NM,
FIRST_VALUE(CC.LSTMOD_BY_AGNT_PRFL_NM) OVER(PARTITION BY CC.CASE_ID)as FST_AGNT_PRFL_NM,
LAST_VALUE(CC.CASE_OWN_NM) OVER(PARTITION BY CC.CASE_ID) as LST_AGNT_CASE_OWN_NM,
LAST_VALUE(CC.LSTMOD_BY_AGNT_PRFL_NM) OVER(PARTITION BY CC.CASE_ID) as LST_AGNT_PRFL_NM
,LAST_VALUE(CC.ESCL_RSN_TXT) OVER(PARTITION BY CC.CASE_ID )as ESCL_RSN_TXT,
LAST_VALUE( CC.ESCL_DTLS_TXT) OVER(PARTITION BY CC.CASE_ID ) as ESCL_DTLS_TXT
from EDW_KATAMARI_T.CNTCT_CASE ) CC INNER JOIN EDW_KATAMARI_T.CNTCT_CASE_EXTN CCE
ON CC.CNTCT_CASE_APND_KEY = CCE.CNTCT_CASE_APND_KEY INNER JOIN EDW_STAGE_COMN_SRC.STG_CNTCT_CASE_DELTA DELTA
on CC.CASE_ID = DELTA.CASE_ID
group by 1,2,3,4,5 ,6,7
sample 1
这篇关于在 group by 子句中不允许使用 Teradata Orderer 分析函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文