SELECT 6,(SELECT decode(month(Sysdate),1,sum1.s-sum2.s,sum1.s-sum2.s+sum3.s-sum4.s)
FROM
(SELECT sum(lsi.ONE_MONTH_AMT) s FROM XX_LEDGER_STAT_IFS lsi
WHERE
EXISTS
(SELECT 1 FROM XX_PA_GEN_FTP_UNIT_ID pg
WHERE pg.INSTRUMENT_TYPE_CD = 560
AND pg.GL_ACCT_ID = lsi.GL_ACCT_ID)
AND substr(lsi.GL_ACCT_ID,1,1) = '4'
GROUP BY
lsi.ISOCRNCYCD,
lsi.ORG_ID,
lsi.DEPT_ID,
lsi.GL_ACCT_ID) sum1,
(SELECT sum(lsi.ONE_MONTH_AMT) s FROM XX_LEDGER_STAT_IFS lsi
WHERE
EXISTS
(SELECT 1 FROM XX_PA_GEN_FTP_UNIT_ID pg
WHERE pg.INSTRUMENT_TYPE_CD = 560
AND pg.GL_ACCT_ID = lsi.GL_ACCT_ID)
AND substr(lsi.GL_ACCT_ID,1,1) = '5'
GROUP BY
lsi.ISOCRNCYCD,
lsi.ORG_ID,
lsi.DEPT_ID,
lsi.GL_ACCT_ID) sum2,
(SELECT sum(ytd_n) s FROM LEDGER_STAT ls
WHERE
EXISTS
(SELECT 1 FROM XX_PA_GEN_FTP_UNIT_ID pg
WHERE pg.INSTRUMENT_TYPE_CD = 560
AND pg.GL_ACCT_ID = ls.GL_ACCOUNT_ID)
AND substr(ls.GL_ACCOUNT_ID,1,1) = '4'
AND ls.YEAR_S = xx.YEAR_S
GROUP BY
ls.ISO_CURRENCY_CD,
ls.ORG_UNIT_ID,
ls.DEPT_ID,
ls.GL_ACCOUNT_ID) sum3,
(SELECT sum(ytd_n) s FROM LEDGER_STAT ls
WHERE
EXISTS
(SELECT 1 FROM XX_PA_GEN_FTP_UNIT_ID pg
WHERE pg.INSTRUMENT_TYPE_CD = 560
AND pg.GL_ACCT_ID = ls.GL_ACCOUNT_ID)
AND substr(ls.GL_ACCOUNT_ID,1,1) = '5'
AND ls.YEAR_S = xx.YEAR_S
GROUP BY
ls.ISO_CURRENCY_CD,
ls.ORG_UNIT_ID,
ls.DEPT_ID,
ls.GL_ACCOUNT_ID) sum4),
xx.ORG_ID
FROM XX_LEDGER_STAT_IFS xx; 报错:
PROCEDURE BOCOFSA.PROC_FPF_WBSYYJ 编译错误错误:PL/SQL: ORA-00904: "XX"."YEAR_S": invalid identifier
行:103
文本:AND ls.YEAR_S = xx.YEAR_S是什么原因?子查询的子查询就不能引用父查询的表了?
FROM
(SELECT sum(lsi.ONE_MONTH_AMT) s FROM XX_LEDGER_STAT_IFS lsi
WHERE
EXISTS
(SELECT 1 FROM XX_PA_GEN_FTP_UNIT_ID pg
WHERE pg.INSTRUMENT_TYPE_CD = 560
AND pg.GL_ACCT_ID = lsi.GL_ACCT_ID)
AND substr(lsi.GL_ACCT_ID,1,1) = '4'
GROUP BY
lsi.ISOCRNCYCD,
lsi.ORG_ID,
lsi.DEPT_ID,
lsi.GL_ACCT_ID) sum1,
(SELECT sum(lsi.ONE_MONTH_AMT) s FROM XX_LEDGER_STAT_IFS lsi
WHERE
EXISTS
(SELECT 1 FROM XX_PA_GEN_FTP_UNIT_ID pg
WHERE pg.INSTRUMENT_TYPE_CD = 560
AND pg.GL_ACCT_ID = lsi.GL_ACCT_ID)
AND substr(lsi.GL_ACCT_ID,1,1) = '5'
GROUP BY
lsi.ISOCRNCYCD,
lsi.ORG_ID,
lsi.DEPT_ID,
lsi.GL_ACCT_ID) sum2,
(SELECT sum(ytd_n) s FROM LEDGER_STAT ls
WHERE
EXISTS
(SELECT 1 FROM XX_PA_GEN_FTP_UNIT_ID pg
WHERE pg.INSTRUMENT_TYPE_CD = 560
AND pg.GL_ACCT_ID = ls.GL_ACCOUNT_ID)
AND substr(ls.GL_ACCOUNT_ID,1,1) = '4'
AND ls.YEAR_S = xx.YEAR_S
GROUP BY
ls.ISO_CURRENCY_CD,
ls.ORG_UNIT_ID,
ls.DEPT_ID,
ls.GL_ACCOUNT_ID) sum3,
(SELECT sum(ytd_n) s FROM LEDGER_STAT ls
WHERE
EXISTS
(SELECT 1 FROM XX_PA_GEN_FTP_UNIT_ID pg
WHERE pg.INSTRUMENT_TYPE_CD = 560
AND pg.GL_ACCT_ID = ls.GL_ACCOUNT_ID)
AND substr(ls.GL_ACCOUNT_ID,1,1) = '5'
AND ls.YEAR_S = xx.YEAR_S
GROUP BY
ls.ISO_CURRENCY_CD,
ls.ORG_UNIT_ID,
ls.DEPT_ID,
ls.GL_ACCOUNT_ID) sum4),
xx.ORG_ID
FROM XX_LEDGER_STAT_IFS xx; 报错:
PROCEDURE BOCOFSA.PROC_FPF_WBSYYJ 编译错误错误:PL/SQL: ORA-00904: "XX"."YEAR_S": invalid identifier
行:103
文本:AND ls.YEAR_S = xx.YEAR_S是什么原因?子查询的子查询就不能引用父查询的表了?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货