Oracle中的QUALIFY子句 [英] Qualify clause in Oracle
本文介绍了Oracle中的QUALIFY子句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我参与了Teradata到Oracle的迁移项目。如何修改以下在Teradata中使用Qualify的查询。
//查询%1
SELECT S.ID as Id,
S.MP_CD as Code,
S.GM_CD as GmCode,
S.GM_MSR_NBR as Mea_Year,
S.STTS_CD as YearCode,
S.TRMNTN_DTM as TerminationDate
FROM PD.RVY S, LOAD_LOG TLL
WHERE S.UPDTD_LOAD = TLL.LOG_KEY AND TLL.BLSH_CD = 'Y' AND S.STTS_CD IN ( 'C', 'P' )
QUALIFY ROW_NUMBER () OVER (PARTITION BY S.GM_CD ,S.MP_CD ,S.GM_MSR_NBR,S.STTS_CD
ORDER BY S.SO_DTM DESC
) = 1;
//查询2
SELECT SP.ID,
SP.SO_DTM,
SP.TAX_ID,
SP.USER_ID,
SP.FRST_NM,
SP.LAST_NM,
SP.PHONE_NBR,
QSRP.TAX_ID,
QSRP.ROW_ID,
MAX(SP.SO_DTM) OVER (PARTITION BY SP.ID, SP.TAX_ID) MAX_SO_DTM
FROM VOPR_RMSY SP,VOPR_RMSY_SPNS QSRP
WHERE SP.ID =:URVYID AND QSRP.TAX_ID =:RPAXID
AND SP.ID = QSRP.ID AND SP.TAX_ID = QSRP.TAX_ID AND SP.SO_DTM = QSRP.SO_DTM
QUALIFY (SP.SO_DTM=MAX_SO_DTM AND QSRP.SO_DTM = MAX_SO_DTM)
GROUP BY SP.ID,SP.SO_DTM,SP.TAX_ID,SP.USER_ID,SP.FRST_NM,SP.LAST_NM,SP.PHONE_NBR,
QSRP.TAX_ID,QSRP.ROW_ID;
为此,尝试使用HAVE而不是QUALIZY,但收到错误:
ORA-00904:"MAX_SO_DTM":无效的标识符 00904。00000-"%s:无效的标识符"
似乎Max使用的别名在这里不起作用...
非常感谢您的帮助!
编辑:
SELECT * FROM
(
SP.ID,
SP.SO_DTM,
SP.TAX_ID,
SP.USER_ID,
SP.FRST_NM,
SP.LAST_NM,
SP.PHONE_NBR,
QSRP.TAX_ID,
QSRP.ROW_ID,
MAX(SP.SO_DTM) OVER (PARTITION BY SP.ID, SP.TAX_ID) AS MAX_SO_DTM
FROM VOPR_RMSY SP,VOPR_RMSY_SPNS QSRP
WHERE SP.ID =:URVYID AND QSRP.TAX_ID =:RPAXID AND SP.ID = QSRP.ID AND SP.TAX_ID =
QSRP.TAX_ID AND SP.SO_DTM = QSRP.SO_DTM
GROUP BY SP.ID,SP.SO_DTM,SP.TAX_ID,SP.USER_ID,SP.FRST_NM,SP.LAST_NM,SP.PHONE_NBR,
QSRP.TAX_ID,QSRP.ROW_ID;
)dt WHERE (SP.SO_DTM=MAX_SO_DTM AND QSRP.SO_DTM = MAX_SO_DTM)
我知道我必须使用别名DT来代替SP和QSRP作为OUTER WHERE的别名,但是这里的MAX_SO_DTM与来自两个不同表的SO_DTM进行了比较。是否有其他方法可以修改此内容?
谢谢!
Teradata
限定和重复使用别名都是推荐答案特定的。
您尝试HAVING失败,因为这是处理查询的逻辑顺序:
FROM
WHERE
GROUP BY
HAVING
OLAP-function
QUALIFY -- Teradata specific
SAMPLE or EXPAND ON -- both are Teradata specific
ORDER
若要解决此问题,您必须使用派生表/内联视图,并将限定条件移到外部WHERE中。
SELECT *
FROM
(
SELECT S.ID as Id,
S.MP_CD as Code,
S.GM_CD as GmCode,
S.GM_MSR_NBR as Mea_Year,
S.STTS_CD as YearCode,
S.TRMNTN_DTM as TerminationDate,
ROW_NUMBER () OVER (PARTITION BY S.GM_CD ,S.MP_CD ,S.GM_MSR_NBR,S.STTS_CD
ORDER BY S.SO_DTM DESC) AS rn
FROM PD.RVY S, LOAD_LOG TLL
WHERE S.UPDTD_LOAD = TLL.LOG_KEY AND TLL.BLSH_CD = 'Y' AND S.STTS_CD IN ( 'C', 'P' )
) dt
WHERE rn = 1;
如果要重复使用别名,则必须使用相同的技术。
编辑: 第二个查询可以重写为:
SELECT *
FROM
(
SELECT
SP.ID,
SP.SO_DTM,
SP.TAX_ID,
SP.USER_ID,
SP.FRST_NM,
SP.LAST_NM,
SP.PHONE_NBR,
QSRP.TAX_ID,
QSRP.ROW_ID,
MAX(SP.SO_DTM) OVER (PARTITION BY SP.ID, SP.TAX_ID) AS MAX_SO_DTM
FROM VOPR_RMSY SP,VOPR_RMSY_SPNS QSRP
WHERE SP.ID =:URVYID
AND QSRP.TAX_ID =:RPAXID
AND SP.ID = QSRP.ID
AND SP.TAX_ID = QSRP.TAX_ID
AND SP.SO_DTM = QSRP.SO_DTM
GROUP BY SP.ID,SP.SO_DTM,SP.TAX_ID,SP.USER_ID,SP.FRST_NM,SP.LAST_NM,SP.PHONE_NBR,
QSRP.TAX_ID,QSRP.ROW_ID
)dt
WHERE SO_DTM=MAX_SO_DTM
您不需要同时执行两个比较*(SP.SO_DTM=MAX_SO_DTM和QSRP.SO_DTM=MAX_SO_DTM)*,因为第二个比较是多余的,因为表在*SP.SO_DTM=QSRP.SO_DTM*上连接。
否则,您必须使用不同的别名将QSRP.SO_DTM添加到派生表中(例如,QSRP_SO_DTM)。在外层,不再有SP/QSRP,只有DT,因此它将是:WHERE (SO_DTM=MAX_SO_DTM AND QSRP_SO_DTM = MAX_SO_DTM)
这篇关于Oracle中的QUALIFY子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文