Echoing wat Ed said,
its too hard to understand a 2 page query over an email .. so may be
something like
select * from (select blah from abc where condition)a join (select blah
from xyz where condition)b on (blah) where condition may help to understand
On Thu, Oct 17, 2013 at 2:51 PM, Ed Soniat <eson...@liveperson.com> wrote:
> 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.
>
--
Nitin Pawar
