Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Where clause position


Copy link to this message
-
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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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

Nitin Pawar