db2 with函数使用

elixiat

WITH T1 AS(
SELECT CGBT.SEGMENT1,
CGBT.SEGMENT3,
(SELECT SRC_COD_VALUE FROM F_COM_CODE_CONV WHERE SRC_CODE_TYPE='FIN000000' AND TAR_CODE_VALUE=CGBT.CURRENCY_CODE) CURRENCY_CODE,
TO_CHAR(CGBT.ACCOUNTING_DATE,'YYYY-MM-DD') ACCOUNTING_DATE,
B.DR_CR_FLAG,
NVL(SUM(CASE WHEN TO_CHAR(CGBT.ACCOUNTING_DATE,'MM-DD') LIKE '12-31' THEN
CASE WHEN CGBT.PERIOD LIKE '%-13' THEN CGBT.DR_END_BAL ELSE 0 END ELSE CGBT.DR_END_BAL END ),0) DR_END_BAL,
NVL(SUM(CASE WHEN TO_CHAR(CGBT.ACCOUNTING_DATE,'MM-DD') LIKE '12-31' THEN
CASE WHEN CGBT.PERIOD LIKE '%-13' THEN CGBT.CR_END_BAL ELSE 0 END ELSE CGBT.CR_END_BAL END ),0) CR_END_BAL
FROM F_INN_GLG_CUX_GL_BALANCES_T CGBT ,
(SELECT DISTINCT FFVV.ATTRIBUTE10 DR_CR_FLAG,FFVV.FLEX_VALUE
FROM T_V_FIN_FND_FLEX_VALUES FFVV,F_COM_INN_FND_FLEX_VALUE_SETS FFVS
WHERE FFVS.FLEX_VALUE_SET_ID = FFVV.FLEX_VALUE_SET_ID AND FFVS.FLEX_VALUE_SET_NAME = 'GLB_COA_ACCOUNTING') B
WHERE B.FLEX_VALUE = CGBT.SEGMENT3 AND CGBT.ACCOUNTING_DATE = TO_DATE('#WORK_DAY#','YYYY-MM-DD')
GROUP BY CGBT.SEGMENT1,CGBT.SEGMENT3,CGBT.CURRENCY_CODE,CGBT.ACCOUNTING_DATE,B.DR_CR_FLAG
),
T2 AS(
SELECT SEGMENT1,SEGMENT3,(SELECT SRC_COD_VALUE FROM F_COM_CODE_CONV WHERE SRC_CODE_TYPE='FIN000000' AND TAR_CODE_VALUE=CGBT.CURRENCY_CODE) CURRENCY_CODE,
NVL(SUM(CGBT.DR_BEGIN_BAL),0) DR_BEGIN_BAL,
NVL(SUM(CGBT.CR_BEGIN_BAL),0) CR_BEGIN_BAL
--INTO V_DR_BEGIN_BAL,V_CR_BEGIN_BAL
FROM F_INN_GLG_CUX_GL_BALANCES_T CGBT
WHERE (CASE WHEN  TO_CHAR(CGBT.ACCOUNTING_DATE,'MM-DD') LIKE '12-31' THEN CASE WHEN CGBT.PERIOD LIKE '%-12' THEN 1 ELSE 0 END ELSE 1 END) = 1
AND CGBT.ACCOUNTING_DATE = TO_DATE(SUBSTR('#WORK_DAY#',1,6)||'01','YYYY-MM-DD')
GROUP BY SEGMENT1,SEGMENT3,CURRENCY_CODE
),
T3 AS(
SELECT SEGMENT1,SEGMENT3,(SELECT SRC_COD_VALUE FROM F_COM_CODE_CONV WHERE SRC_CODE_TYPE='FIN000000' AND TAR_CODE_VALUE=CGBT.CURRENCY_CODE) CURRENCY_CODE,
NVL(SUM(CGBT.DR_BEGIN_BAL),0) PRE_DR_END_BAL,
NVL(SUM(CGBT.CR_BEGIN_BAL),0) PRE_CR_END_BAL
--INTO PRE_DR_END_BAL,PRE_CR_END_BAL
FROM F_INN_GLG_CUX_GL_BALANCES_T CGBT
WHERE (CASE WHEN  TO_CHAR(CGBT.ACCOUNTING_DATE,'MM-DD') LIKE '12-31' THEN CASE WHEN CGBT.PERIOD LIKE '%-12' THEN 0 ELSE 1 END ELSE 1 END) = 1      
AND CGBT.ACCOUNTING_DATE = TO_DATE(SUBSTR('#WORK_DAY#',1,6)||'01','YYYY-MM-DD') - 1 DAYS
GROUP BY SEGMENT1,SEGMENT3,CURRENCY_CODE
),
T4 AS(
SELECT SEGMENT1,SEGMENT3,(SELECT SRC_COD_VALUE FROM F_COM_CODE_CONV WHERE SRC_CODE_TYPE='FIN000000' AND TAR_CODE_VALUE=CGBT.CURRENCY_CODE) CURRENCY_CODE,
NVL(SUM(CGBT.DR_BEGIN_BAL),0) YEAR_DR_BEGIN_BAL,
NVL(SUM(CGBT.CR_BEGIN_BAL),0) YEAR_CR_BEGIN_BAL
--INTO YEAR_DR_BEGIN_BAL,YEAR_CR_BEGIN_BAL
FROM F_INN_GLG_CUX_GL_BALANCES_T CGBT
WHERE CGBT.ACCOUNTING_DATE = TO_DATE(SUBSTR('#WORK_DAY#',1,4)||'0101','YYYY-MM-DD')
GROUP BY SEGMENT1,SEGMENT3,CURRENCY_CODE
)
SELECT T1.SEGMENT1 JGDH,
T1.SEGMENT3 KMDH,
T1.CURRENCY_CODE HB,
T1.ACCOUNTING_DATE RQ,
CAST(CASE WHEN T1.DR_CR_FLAG = 'DR' THEN NVL(T1.DR_END_BAL,0)-NVL(T1.CR_END_BAL,0) WHEN T1.DR_CR_FLAG = 'CR' THEN 0
ELSE CASE WHEN NVL(T1.DR_END_BAL,0)-NVL(T1.CR_END_BAL,0) >=0 THEN NVL(T1.DR_END_BAL,0)-NVL(T1.CR_END_BAL,0) ELSE 0 END END AS DECIMAL(20,2)) JFYE,
CAST(CASE WHEN T1.DR_CR_FLAG = 'DR' THEN 0 WHEN T1.DR_CR_FLAG = 'CR' THEN NVL(T1.CR_END_BAL,0)-NVL(T1.DR_END_BAL,0)
ELSE CASE WHEN NVL(T1.CR_END_BAL,0)-NVL(T1.DR_END_BAL,0) >=0 THEN NVL(T1.CR_END_BAL,0)-NVL(T1.DR_END_BAL,0) ELSE 0 END END AS DECIMAL(20,2)) DFYE,
CAST(NVL(T1.DR_END_BAL,0) - NVL(T2.DR_BEGIN_BAL,0) AS DECIMAL(20,2)) JFFSE,
CAST(NVL(T1.CR_END_BAL,0) - NVL(T2.CR_BEGIN_BAL,0) AS DECIMAL(20,2)) DFFSE,
CAST(CASE WHEN T1.DR_CR_FLAG = 'DR' THEN NVL(T3.PRE_DR_END_BAL,0)-NVL(T3.PRE_CR_END_BAL,0) WHEN T1.DR_CR_FLAG = 'CR' THEN 0
ELSE CASE WHEN NVL(T3.PRE_DR_END_BAL,0)-NVL(T3.PRE_CR_END_BAL,0) >=0 THEN NVL(T3.PRE_DR_END_BAL,0)-NVL(T3.PRE_CR_END_BAL,0) ELSE 0 END END AS DECIMAL(20,2)) SQJFYE,
CAST(CASE WHEN T1.DR_CR_FLAG = 'DR' THEN NVL(T3.PRE_CR_END_BAL,0)-NVL(T3.PRE_DR_END_BAL,0) WHEN T1.DR_CR_FLAG = 'CR' THEN 0
ELSE CASE WHEN NVL(T3.PRE_CR_END_BAL,0)-NVL(T3.PRE_DR_END_BAL,0) >=0 THEN NVL(T3.PRE_CR_END_BAL,0)-NVL(T3.PRE_DR_END_BAL,0) ELSE 0 END END AS DECIMAL(20,2)) SQDFYE,
CAST(NVL(T1.DR_END_BAL,0) - NVL(T4.YEAR_DR_BEGIN_BAL,0) AS DECIMAL(20,2)) NLJJFFSE,
CAST(NVL(T1.CR_END_BAL,0) - NVL(T4.YEAR_CR_BEGIN_BAL,0) AS DECIMAL(20,2)) NLJDFFSE,
CAST(CASE WHEN T1.DR_CR_FLAG = 'DR' THEN NVL(T4.YEAR_DR_BEGIN_BAL,0)-NVL(T4.YEAR_CR_BEGIN_BAL,0) WHEN T1.DR_CR_FLAG = 'CR' THEN 0
ELSE CASE WHEN NVL(T4.YEAR_DR_BEGIN_BAL,0)-NVL(T4.YEAR_CR_BEGIN_BAL,0) >=0 THEN NVL(T4.YEAR_DR_BEGIN_BAL,0)-NVL(T4.YEAR_CR_BEGIN_BAL,0) ELSE 0 END END AS DECIMAL(20,2)) NCJFYE,
CAST(CASE WHEN T1.DR_CR_FLAG = 'DR' THEN NVL(T4.YEAR_CR_BEGIN_BAL,0)-NVL(T4.YEAR_DR_BEGIN_BAL,0) WHEN T1.DR_CR_FLAG = 'CR' THEN 0
ELSE CASE WHEN NVL(T4.YEAR_CR_BEGIN_BAL,0)-NVL(T4.YEAR_DR_BEGIN_BAL,0) >=0 THEN NVL(T4.YEAR_CR_BEGIN_BAL,0)-NVL(T4.YEAR_DR_BEGIN_BAL,0) ELSE 0 END END AS DECIMAL(20,2)) NCDFYE
FROM T1 LEFT JOIN
T2 ON T1.SEGMENT1=T2.SEGMENT1 AND T1.SEGMENT3=T2.SEGMENT3 AND T1.CURRENCY_CODE=T2.CURRENCY_CODE LEFT JOIN
T3 ON T1.SEGMENT1=T3.SEGMENT1 AND T1.SEGMENT3=T3.SEGMENT3 AND T1.CURRENCY_CODE=T3.CURRENCY_CODE LEFT JOIN
T4 ON T1.SEGMENT1=T4.SEGMENT1 AND T1.SEGMENT3=T4.SEGMENT3 AND T1.CURRENCY_CODE=T4.CURRENCY_CODE