SELECT *
FROM
(
SELECT DATE_KEY,CELL_NAME, HOUR_KEY,HANDOVER_PREPARATION_RATE_EUCELL_ERIC_
FROM EDW.FCT_PRSE4G_CELL_KPI_H
WHERE PROVINCE='KJ'
AND DATE_KEY BETWEEN TO_CHAR(TO_DATE(SYSDATE,'DD/MM/YY') -2,'YYYYMMDD') AND TO_CHAR(TO_DATE(SYSDATE,'DD/MM/YY') -1,'YYYYMMDD')
AND HOUR_KEY BETWEEN 0AND 8
)
PIVOT (
SUM(HANDOVER_PREPARATION_RATE_EUCELL_ERIC_) FOR DATE_key IN ( TO_CHAR(TO_DATE(SYSDATE,'DD/MM/YY') -2,'YYYYMMDD') AS HAND_48, TO_CHAR(TO_DATE(SYSDATE,'DD/MM/YY') -1,'YYYYMMDD') AS HAND_24)
) PVT1错误是关于date_key和IN cluas .我在IN（）中使用了子查询，错误是：ORA-00936“select”缺少表达式
SELECT *
FROM
(
SELECT DATE_KEY,CELL_NAME, HOUR_KEY,HANDOVER_PREPARATION_RATE_EUCELL_ERIC_
FROM EDW.FCT_PRSE4G_CELL_KPI_H
WHERE PROVINCE='KJ'
AND DATE_KEY BETWEEN TO_CHAR(TO_DATE(SYSDATE,'DD/MM/YY') -2,'YYYYMMDD') AND TO_CHAR(TO_DATE(SYSDATE,'DD/MM/YY') -1,'YYYYMMDD')
AND HOUR_KEY BETWEEN 0AND 8
)
PIVOT (
SUM(HANDOVER_PREPARATION_RATE_EUCELL_ERIC_) FOR DATE_key IN ( select TO_CHAR(TO_DATE(SYSDATE,'DD/MM/YY') -2,'YYYYMMDD') AS HAND_48, TO_CHAR(TO_DATE(SYSDATE,'DD/MM/YY') -1,'YYYYMMDD') AS HAND_24 from dual)
) PVT1
1条答案
按热度按时间wfypjpf41#
你不能像在
PIVOT中那样使用动态键;但是，您可以找到今天（午夜）和您的date_key之间的天数差异，然后以该数字为中心：DATE数据类型。*DATE的值上使用TO_DATE（例如SYSDATE）。在最好的情况下，它不会做任何事情，但在最坏的情况下，它会引发异常或生成意外的结果。