The following query does not work:
SELECT
T1.ACCOUNT_NUM
,T1.ACCOUNT_MODIFIER_NUM
,T1.DEPOSIT_TYPE_CD
,T1.DEPOSIT_TERM
,CASE
WHEN T1.DEPOSIT_TYPE_CD='5021' THEN
'92550000'
ELSE
CASE
WHEN T4.LEDGER_SUBJECT_ID_01= '00000000' THEN ''
ELSE COALESCE(T4.LEDGER_SUBJECT_ID_01,'')
END
END V_LEDGER_SUBJECT_ID
,COALESCE(T10.INTERNAL_PARTY_ID,'') INTERNAL_PARTY_ID
,T5.SIGNE_DT
,T5.CLOSED_DT
,CASE
WHEN T1.FINANCE_ACCOUNT_TYPE_CD='20' THEN
COALESCE(T6.CURRENCY_CD,SUBSTR(T1.ACCOUNT_MODIFIER_NUM,3,3))
ELSE COALESCE(T7.CURRENCY_CD,SUBSTR(T1.ACCOUNT_MODIFIER_NUM,3,3))
END FINANCE_ACCOUNT_TYPE_CD
,CASE
WHEN T1.FINANCE_ACCOUNT_TYPE_CD='20' THEN
COALESCE(T6.AGT_AMT,0)
ELSE
CASE
WHEN SUBSTR(COALESCE(T4.LEDGER_SUBJECT_ID_01,''),1,2) = '21' AND
T4.LEDGER_SUBJECT_ID_02 = '00000000'
THEN COALESCE(T7.Agt_Amt_003,0)- COALESCE(T7.Agt_Amt_007,0)
ELSE COALESCE(T7.Agt_Amt_003,0)
END
END V_ACCOUNT_BAL1
,CASE
WHEN T1.FINANCE_ACCOUNT_TYPE_CD='20' THEN
CASE
WHEN T8.AGT_DATE<20120515 THEN COALESCE(T6.AGT_AMT,0.0)
ELSE 0.0
END
ELSE 0.0
END V_INNER_MONTH_DELAY_ACCUM1
FROM T03_DEPOSIT_ACCOUNT T1
LEFT OUTER JOIN T03_AGT_SUBSECTION_RELA_H T3
ON T1.ACCOUNT_NUM=T3.ACCOUNT_NUM
AND T1.ACCOUNT_MODIFIER_NUM=T3.ACCOUNT_MODIFIER_NUM
AND T3.START_DATE<=20120515
AND T3.END_DATE>20120515
LEFT OUTER JOIN
( SELECT
Product_Subsection_Id
,MAX(
CASE
WHEN PROD_SUBJ_RELA_TYPE_CD = '01' THEN Ledger_Subject_Id
ELSE '00000000'
END
) Ledger_Subject_Id_01
,MAX(
CASE
WHEN PROD_SUBJ_RELA_TYPE_CD = '02' THEN Ledger_Subject_Id
ELSE '00000000'
END
) Ledger_Subject_Id_02
FROM T98_DC_PRO_SUB_SUBJECT_REF
WHERE PROD_SUBJ_RELA_TYPE_CD IN ('01','02')
GROUP BY Product_Subsection_Id
) T4
ON T3.PRODUCT_SUBSECTION_ID=T4.PRODUCT_SUBSECTION_ID
LEFT OUTER JOIN T03_AGREEMENT T5
ON T1.ACCOUNT_NUM=T5.ACCOUNT_NUM
AND T1.ACCOUNT_MODIFIER_NUM=T5.ACCOUNT_MODIFIER_NUM
LEFT OUTER JOIN T03_AGT_AMOUNT_H T6
ON T1.ACCOUNT_NUM=T6.ACCOUNT_NUM
AND T1.ACCOUNT_MODIFIER_NUM=T6.ACCOUNT_MODIFIER_NUM
AND T6.AGT_AMT_TYPE_CD IN('001','215')
AND T6.START_DATE<=20120515
AND T6.END_DATE>20120515
LEFT OUTER JOIN
(SELECT
Account_Num
,Account_Modifier_Num
,Currency_Cd
,SUM(
CASE
WHEN AGT_AMT_TYPE_CD = '001' THEN COALESCE(Agt_Amt,0.0)
ELSE 0.0
END
) Agt_Amt_001
,SUM(
CASE
WHEN AGT_AMT_TYPE_CD = '003' THEN COALESCE(Agt_Amt,0.0)
ELSE 0.0
END
) Agt_Amt_003
,SUM(
CASE
WHEN AGT_AMT_TYPE_CD = '007' THEN COALESCE(Agt_Amt,0.0)
ELSE 0.0
END
) Agt_Amt_007
FROM T03_AGT_AMOUNT_H_C
WHERE AGT_AMT_TYPE_CD IN ('001','003','007')
AND START_DATE<=20120515
AND END_DATE> 20120515
GROUP BY Account_Num,Account_Modifier_Num,Currency_Cd
) T7
ON T1.ACCOUNT_NUM=T7.ACCOUNT_NUM
AND T1.ACCOUNT_MODIFIER_NUM=T7.ACCOUNT_MODIFIER_NUM
LEFT OUTER JOIN T03_AGT_DATE_H T8
ON T1.ACCOUNT_NUM=T8.ACCOUNT_NUM
AND T1.ACCOUNT_MODIFIER_NUM=T8.ACCOUNT_MODIFIER_NUM
AND T8.Date_Type_Cd='002'
AND T8.START_DATE<=20120515
AND T8.END_DATE>20120515
LEFT SEMI JOIN
( SELECT
Account_Num
,Account_Modifier_Num
,Agt_Status_Type_Cd
,Start_Date
,Agt_Status_Reason_Cd
,Agt_Status_Cd
,End_Date
FROM T03_AGT_STATUS_H
WHERE Agt_Status_Type_Cd='01'
AND Agt_Status_Cd NOT IN('102','N03','AA2')
AND START_DATE<=20120515
AND END_DATE>20120515
)T9
ON T1.ACCOUNT_NUM=T9.ACCOUNT_NUM
AND T1.ACCOUNT_MODIFIER_NUM=T9.ACCOUNT_MODIFIER_NUM
LEFT OUTER JOIN
(
SELECT
Account_Num
,Account_Modifier_Num
,Agt_Party_Rela_Cd
,Start_Date
,Internal_Party_Id
,End_Date
FROM T03_AGT_INTER_ORG_RELA_H
WHERE Start_Date <= 20120515
AND End_Date > 20120515
AND Agt_Party_Rela_Cd = '24'
) T10
ON T1.ACCOUNT_NUM = T10.ACCOUNT_NUM
AND T1.ACCOUNT_MODIFIER_NUM = T10.ACCOUNT_MODIFIER_NUM
WHERE T1.ACCOUNT_MODIFIER_NUM <> '0202'
LIMIT 5;
However, when move "WHERE T1.ACCOUNT_MODIFIER_NUM <> '0202' " into the
innermost layer, add the coalesce, it works.
Can someone please tell me what's the rule here?
Thank you very much!
