Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 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
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB