我试图将SQL语句作为实例化视图运行,然后将表与更大的表连接起来。我遇到了违反较大表中项目/位置组合的重复约束的问题。这是我的物化视图查询:
SELECT /*+ materialize */ CAN.CNCL_CNT, CAN.ITEM, CAN.LOCATION,
CAN.LAST_CNCL_DTE, OL.CANCEL_ID
FROM ORDLOC OL,
(SELECT COUNT(CANCEL_DATE) AS CNCL_CNT, ITEM, LOCATION,
MAX(CANCEL_DATE) AS LAST_CNCL_DTE
FROM ORDLOC
WHERE CANCEL_DATE between (to_date(20160123,'yyyymmdd') -60) and to_date(20160123,'yyyymmdd')
AND CANCEL_DATE IS NOT NULL
GROUP BY ITEM, LOCATION) CAN
WHERE CAN.ITEM = OL.ITEM
AND CAN.LOCATION = OL.LOCATION
AND CAN.LAST_CNCL_DTE = OL.CANCEL_DATE
GROUP BY CAN.CNCL_CNT, CAN.ITEM, CAN.LOCATION, CAN.LAST_CNCL_DTE, OL.CANCEL_ID
现在,当我单独运行嵌套查询时,我少了几百行,并且项/位置组合都是唯一的,但是当运行整个查询时,将有几百行返回重复的项/位置。
解决方案是对CANCEL_ID使用max函数,以消除重复的ITEM / LOCATION。由于在极少数情况下,我具有相同的ITEM,LOCATION,LAST_CNCL_DATE,但具有两个不同的CANCEL_ID。这是我使用的代码。
SELECT CAN.CNCL_CNT, CAN.ITEM, CAN.LOCATION,
CAN.LAST_CNCL_DTE, MAX (OL.CANCEL_ID) AS CANCEL_ID
FROM ORDLOC OL,
(SELECT COUNT(CANCEL_DATE) AS CNCL_CNT, ITEM, LOCATION,
MAX(CANCEL_DATE) AS LAST_CNCL_DTE
FROM ORDLOC
WHERE CANCEL_DATE BETWEEN (TO_DATE(20160123,'yyyymmdd') -60) AND TO_DATE(20160123,'yyyymmdd')
GROUP BY ITEM, LOCATION) CAN
WHERE CAN.ITEM = OL.ITEM
AND CAN.LOCATION = OL.LOCATION
AND CAN.LAST_CNCL_DTE = OL.CANCEL_DATE
GROUP BY CAN.CNCL_CNT, CAN.ITEM, CAN.LOCATION, CAN.LAST_CNCL_DTE
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句