永洪社区

标题: 创建数据集自定义SQL语句报错 [打印本页]

作者: yhdata_TfOCpjFc 时间: 2024-1-5 20:10
标题: 创建数据集自定义SQL语句报错
本帖最后由 yhdata_TfOCpjFc 于 2024-1-5 20:13 编辑

当我把timeWeek标签里的内容删除掉就可以运行查出数据，加上之后就报错，也是很奇怪，sql语句也没啥错误,
发布问题得时候总是提示不良信息也是醉了，下面有timeDay  和 timeWeek，大家费眼找一下哈
select
CASE WHEN TA1.FACTORY is not null THEN  TA1.FACTORY ELSE T3.FACTORY END AS FACTORY,
  TA1.N_TOTAL AS TOTAL_NUM,
TA1.N_FAILE AS FAILE_NUM,
ROUND(TA1.N_RATE*100,2) AS RATE,
ROUND((1-TA1.N_RATE/TA1.Y_RATE)*100,2) AS YOY,
ROUND((1-TA1.N_RATE/T3.RATE)*100,2) AS QOQ

from(
select CASE WHEN T1.FACTORY is not null THEN  T1.FACTORY ELSE T2.FACTORY END AS FACTORY,
  T1.TOTAL AS N_TOTAL,
T1.FAILE AS N_FAILE,
T1.rate AS N_RATE,
   T2.TOTAL AS Y_TOTAL,
T2.FAILE AS Y_FAILE,
T2.rate AS Y_RATE
  from (SELECT
  FACTORY,
  sum( ADS_SC_ZBY01328_V) TOTAL,
  sum( ADS_SC_ZBY01327_V ) FAILE,
sum( ADS_SC_ZBY01327_V ) /sum( ADS_SC_ZBY01328_V) rate
FROM
  EDW_IOCII.ADS_SC_ZBY00453
WHERE

  <timeDay>
UPDATE_DT = ?{timeDay}
  </timeDay>

  <timeWeek>
UPDATE_DT >= TO_CHAR(TRUNC(TO_DATE(?{timeWeek}, 'YYYY-MM-DD'), 'IW'),'YYYY-MM-DD')
AND UPDATE_DT <= TO_CHAR(TRUNC(TO_DATE(?{timeWeek}, 'YYYY-MM-DD'), 'IW') +6,'YYYY-MM-DD')
  </timeWeek>

  GROUP BY FACTORY ORDER BY FACTORY DESC
  ) T1
  FULL OUTER JOIN(

SELECT
  FACTORY,
  sum( ADS_SC_ZBY01328_V) TOTAL,
  sum( ADS_SC_ZBY01327_V ) FAILE,
  sum( ADS_SC_ZBY01327_V ) /sum( ADS_SC_ZBY01328_V) rate
FROM
  EDW_IOCII.ADS_SC_ZBY00453
WHERE
  <timeDay>
UPDATE_DT = TO_CHAR(ADD_MONTHS(TO_DATE(?{timeDay}, 'YYYY-MM-DD'), -12),'YYYY-MM-DD')
  </timeDay>

  GROUP BY FACTORY
  )  T2 ON T1.FACTORY = T2.FACTORY ) TA1
FULL OUTER JOIN
(
SELECT
  FACTORY,
  sum( ADS_SC_ZBY01328_V) TOTAL,
  sum( ADS_SC_ZBY01327_V ) FAILE,
  sum( ADS_SC_ZBY01327_V ) /sum( ADS_SC_ZBY01328_V) rate
FROM
  EDW_IOCII.ADS_SC_ZBY00453
WHERE
  <timeDay>
UPDATE_DT = TO_CHAR(TO_DATE(?{timeDay}, 'YYYY-MM-DD') - 1, 'YYYY-MM-DD')
  </timeDay>

  GROUP BY FACTORY) T3 ON TA1.FACTORY = T3.FACTORY

欢迎光临永洪社区 (https://club.yonghongtech.com/)