Would it be possible to simply this query removing as much as possible
keeping just enough to demonstrate the where issue.
On Wed, Oct 16, 2013 at 2:22 PM, Xiu Guo <xgu...@gmail.com> wrote:
> 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!
>
--
This message may contain confidential and/or privileged information.
If you are not the addressee or authorized to receive this on behalf of the
addressee you must not use, copy, disclose or take action based on this
message or any information herein.
If you have received this message in error, please advise the sender
immediately by reply email and delete this message. Thank you.
