You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Xiu Guo <xg...@gmail.com> on 2013/10/16 20:22:57 UTC
Where clause position
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!
Re: Where clause position
Posted by Xiu Guo <xg...@gmail.com>.
Sorry for the big bomb. I tried my best to simplify the query to this:
*SELECT T1.a, T1.b FROM T1
LEFT SEMI JOIN T9 ON T1.a=T9.a AND T1.b=T9.b
LEFT OUTER JOIN T10 ON T1.a = T10.a AND T1.b = T10.b
WHERE T1.b <> '0202';*
Basically, without the last where clause, everything is fine. But why does
the last where clause fail the whole query?
On Thu, Oct 17, 2013 at 5:58 AM, Clay McDonald <
stuart.mcdonald@bateswhite.com> wrote:
> This query should be broken down so that each piece of the query can be
> tested. Start by running it with just one join condition, then expand it to
> two joins and so on, this will help you isolate the issue.****
>
> ** **
>
> Clay****
>
> ** **
>
> ** **
>
> *From:* Nitin Pawar [mailto:nitinpawar432@gmail.com]
> *Sent:* Thursday, October 17, 2013 5:30 AM
> *To:* user@hive.apache.org
> *Subject:* Re: Where clause position****
>
> ** **
>
> 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 <es...@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 <xg...@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****
>
RE: Where clause position
Posted by Clay McDonald <st...@bateswhite.com>.
This query should be broken down so that each piece of the query can be tested. Start by running it with just one join condition, then expand it to two joins and so on, this will help you isolate the issue.
Clay
From: Nitin Pawar [mailto:nitinpawar432@gmail.com]
Sent: Thursday, October 17, 2013 5:30 AM
To: user@hive.apache.org
Subject: Re: Where clause position
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 <es...@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 <xg...@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
Re: Where clause position
Posted by Nitin Pawar <ni...@gmail.com>.
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 <es...@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 <xg...@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
Re: Where clause position
Posted by Ed Soniat <es...@liveperson.com>.
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 <xg...@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.