Kettle的执行sql脚本的组件

最新推荐文章于 2024-06-03 21:41:03 发布

原创最新推荐文章于 2024-06-03 21:41:03 发布 · 5.3k 阅读

0 ·

CC 4.0 BY-SA版权

探讨了在Kettle中使用执行SQL脚本组件时遇到的问题，特定的SQL脚本结构能够成功保存并执行，而另一些则不能。强调了正确的SQL脚本格式对于组件运行的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Kettle的执行sql脚本的组件要是下面的上边sql会报错误,无法保存成功,下面的下边sql就能保存成功,这个组件对于sql是有限制的!!!

UPDATE DSJYDD.T_CSKQZLFX_DQST_YLTS B SET B.YLTS = (
SELECT C.YLTS FROM (
SELECT COUNT(*) YLTS,A.CITYCODE FROM TENV.AIR_CITYDAYAQI_PUBLISH A WHERE A.QUALITY IN ('优','良')
AND TO_CHAR(A.TIMEPOINT,'YYYY-MM') >= (
SELECT CONCAT(YEAR(SYSDATE),'-01') FROM DUAL
)
AND TO_CHAR(A.TIMEPOINT,'YYYY-MM') <= TO_CHAR(SYSDATE,'YYYY-MM')
AND
A.CITYCODE IN ('110000','120000','130100','130200','131000','130600',
'130900','131100','130500','130400','140100'
,'140300','140400','140500','370100','370300','370800','371400','371500','371600','371700','410100','410200'
,'410500','410600','410700','410800','410900')
GROUP BY A.CITYCODE) C WHERE C.CITYCODE = B.CITYCODE),
B.YLTSTB = (SELECT CASE WHEN YLTS_QN = 0 THEN NULL ELSE ROUND((YLTS_JN-YLTS_QN)/YLTS_QN,2) * 100 END YLTSTB FROM (
SELECT COUNT(*) YLTS_JN,E.CITYCODE,
( SELECT YLTS_QN FROM (
SELECT COUNT(*) YLTS_QN,P.CITYCODE FROM TENV.AIR_CITYDAYAQI_PUBLISH P
WHERE P.QUALITY IN ('优','良')
AND TO_CHAR(P.TIMEPOINT,'YYYY-MM') >= TO_CHAR(TO_DATE(CONCAT(YEAR(SYSDATE),'-01'),'YYYY-MM') - 1 YEAR,'YYYY-MM')
AND TO_CHAR(P.TIMEPOINT,'YYYY-MM-DD') <= TO_CHAR((SYSDATE-1 DAY) -1 YEAR,'YYYY-MM-DD')
AND P.CITYCODE IN ('110000','120000','130100','130200','131000','130600',
'130900','131100','130500','130400','140100'
,'140300','140400','140500','370100','370300','370800','371400','371500','371600','371700','410100','410200'
,'410500','410600','410700','410800','410900')
GROUP BY P.CITYCODE
) D WHERE D.CITYCODE = E.CITYCODE) YLTS_QN

FROM TENV.AIR_CITYDAYAQI_PUBLISH E WHERE E.QUALITY IN ('优','良')
AND TO_CHAR(E.TIMEPOINT,'YYYY-MM') >= (
SELECT CONCAT(YEAR(SYSDATE),'-01') FROM DUAL)
AND TO_CHAR(E.TIMEPOINT,'YYYY-MM') <= TO_CHAR(SYSDATE,'YYYY-MM')
AND E.CITYCODE IN ('110000','120000','130100','130200','131000','130600',
'130900','131100','130500','130400','140100'
,'140300','140400','140500','370100','370300','370800','371400','371500','371600','371700','410100','410200'
,'410500','410600','410700','410800','410900')
GROUP BY E.CITYCODE
) F WHERE F.CITYCODE = B.CITYCODE)
WHERE EXISTS (SELECT 1 FROM TENV.AIR_CITYDAYAQI_PUBLISH A WHERE A.QUALITY IN ('优','良')
AND TO_CHAR(A.TIMEPOINT,'YYYY-MM') >= (
SELECT CONCAT(YEAR(SYSDATE),'-01') FROM DUAL
)
AND TO_CHAR(A.TIMEPOINT,'YYYY-MM') <= TO_CHAR(SYSDATE,'YYYY-MM')
AND A.CITYCODE IN ('110000','120000','130100','130200','131000','130600',
'130900','131100','130500','130400','140100'
,'140300','140400','140500','370100','370300','370800','371400','371500','371600','371700','410100','410200'
,'410500','410600','410700','410800','410900')

)

UPDATE DSJYDD.T_CSKQZLFX_DQST_YLTS B SET B.YLTS = (
SELECT C.YLTS FROM (
SELECT COUNT(*) YLTS,A.CITYCODE FROM TENV.AIR_CITYDAYAQI_PUBLISH A WHERE A.QUALITY IN ('优','良')
AND TO_CHAR(A.TIMEPOINT,'YYYY-MM') >= (
SELECT CONCAT(YEAR(SYSDATE),'-01') FROM DUAL
)
AND TO_CHAR(A.TIMEPOINT,'YYYY-MM') <= TO_CHAR(SYSDATE,'YYYY-MM')
GROUP BY A.CITYCODE) C WHERE C.CITYCODE = B.CITYCODE),
B.YLTSTB = (SELECT CASE WHEN YLTS_QN = 0 THEN NULL ELSE ROUND((YLTS_JN-YLTS_QN)/YLTS_QN,2) * 100 END YLTSTB FROM (
SELECT COUNT(*) YLTS_JN,E.CITYCODE,
( SELECT YLTS_QN FROM (
SELECT COUNT(*) YLTS_QN,P.CITYCODE FROM TENV.AIR_CITYDAYAQI_PUBLISH P
WHERE P.QUALITY IN ('优','良')
AND TO_CHAR(P.TIMEPOINT,'YYYY-MM') >= TO_CHAR(TO_DATE(CONCAT(YEAR(SYSDATE),'-01'),'YYYY-MM') - 1 YEAR,'YYYY-MM')
AND TO_CHAR(P.TIMEPOINT,'YYYY-MM-DD') <= TO_CHAR((SYSDATE-1 DAY) -1 YEAR,'YYYY-MM-DD')
GROUP BY P.CITYCODE
) D WHERE D.CITYCODE = E.CITYCODE) YLTS_QN

FROM TENV.AIR_CITYDAYAQI_PUBLISH E WHERE E.QUALITY IN ('优','良')
AND TO_CHAR(E.TIMEPOINT,'YYYY-MM') >= (
SELECT CONCAT(YEAR(SYSDATE),'-01') FROM DUAL)
AND TO_CHAR(E.TIMEPOINT,'YYYY-MM') <= TO_CHAR(SYSDATE,'YYYY-MM')
GROUP BY E.CITYCODE
) F WHERE F.CITYCODE = B.CITYCODE)
WHERE EXISTS (SELECT 1 FROM TENV.AIR_CITYDAYAQI_PUBLISH A WHERE A.QUALITY IN ('优','良')
AND TO_CHAR(A.TIMEPOINT,'YYYY-MM') >= (
SELECT CONCAT(YEAR(SYSDATE),'-01') FROM DUAL
)
AND TO_CHAR(A.TIMEPOINT,'YYYY-MM') <= TO_CHAR(SYSDATE,'YYYY-MM')
)