Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 89D6D107B3 for ; Thu, 17 Oct 2013 09:30:42 +0000 (UTC) Received: (qmail 86641 invoked by uid 500); 17 Oct 2013 09:30:34 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 86595 invoked by uid 500); 17 Oct 2013 09:30:32 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 86587 invoked by uid 99); 17 Oct 2013 09:30:31 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 17 Oct 2013 09:30:31 +0000 X-ASF-Spam-Status: No, hits=2.5 required=5.0 tests=FREEMAIL_ENVFROM_END_DIGIT,HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS,UPPERCASE_50_75 X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of nitinpawar432@gmail.com designates 209.85.128.41 as permitted sender) Received: from [209.85.128.41] (HELO mail-qe0-f41.google.com) (209.85.128.41) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 17 Oct 2013 09:30:25 +0000 Received: by mail-qe0-f41.google.com with SMTP id x7so365813qeu.14 for ; Thu, 17 Oct 2013 02:30:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=+Dwg+XUhHmowQfCLefaa8QmkESgw6+YsqpvjUwoOp1E=; b=rYb/AmwxYPNNF/dNSwi9qxWXzUdG+e2eR+k1iLQDwfcAqL7CYoM2gIbpncazgKidJa 8kfKm0kutXaMbFUEF40ulrdKwlNnRTxupWrsriBFupGzzn1SZHyad7ro/QBEMvL7n48Q AMqbWr0If1IgVBAK67MAd87WYgDof2aFjwKKxnpnClYOc/GzbEfVYP/Z6vWbw7w1qk4Z mZbOXJJdwMfByAW2CuV76KZpZ/6iRVY0Yt7XZ3dtBFzCwKH4kNZbYgmk2qi9k6DFZ05X EqHI8skdbJNcACLwIIa1k8S1uP62985PtgwjkIxT7EvSpnr+ntTCzBHzhuMp08vhoeJX 3wpw== MIME-Version: 1.0 X-Received: by 10.49.4.199 with SMTP id m7mr2412970qem.19.1382002204620; Thu, 17 Oct 2013 02:30:04 -0700 (PDT) Received: by 10.224.193.136 with HTTP; Thu, 17 Oct 2013 02:30:04 -0700 (PDT) In-Reply-To: References: Date: Thu, 17 Oct 2013 15:00:04 +0530 Message-ID: Subject: Re: Where clause position From: Nitin Pawar To: "user@hive.apache.org" Content-Type: multipart/alternative; boundary=047d7bdc10187c3c0a04e8ec76d4 X-Virus-Checked: Checked by ClamAV on apache.org --047d7bdc10187c3c0a04e8ec76d4 Content-Type: text/plain; charset=ISO-8859-1 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 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 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 --047d7bdc10187c3c0a04e8ec76d4 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Echoing wat Ed said,=A0

its too hard to= understand a 2 page query over an email .. so may be something like=A0
select * from (select blah from abc where condition)a join (select b= lah from xyz where condition)b on (blah) where condition may help to unders= tand=A0


On Thu,= Oct 17, 2013 at 2:51 PM, Ed Soniat <esoniat@liveperson.com> wrote:
Would it be possible to sim= ply this query removing as much as possible keeping just enough to demonstr= ate the where issue.


On Wed, Oct 16, 2013 at 2:22 PM, Xiu Guo <xguo27@gmail.com>= ; wrote:
The followin= g 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=3D'5021' THEN

'92550000'

ELSE

CASE

WHEN T4.LEDGER_SUBJECT_ID_01=3D '00000000' THEN '= ;'

ELSE COALESCE(T4.LEDGER_SUBJECT_ID_01,'')

END

END V_LEDGER_SUBJECT_ID

,COALESCE(T10.INTERNAL_PARTY_ID,'') INTERNAL_PARTY_I= D

,T5.SIGNE_DT

,T5.CLOSED_DT

,CASE

WHEN T1.FINANCE_ACCOUNT_TYPE_CD=3D'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=3D'20' THEN

COALESCE(T6.AGT_AMT,0)

ELSE

CASE

WHEN SUBSTR(COALESCE(T4.LEDGER_SUBJECT_ID_01,''),1,2= ) =3D '21' AND T4.LEDGER_SUBJECT_ID_02 =3D '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=3D'20' THEN

CASE

WHEN T8.AGT_DATE<20120515 THEN COALESCE(T6.AGT_AMT,0.0) <= /p>

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=3DT3.ACCOUNT_NUM

AND T1.ACCOUNT_MODIFIER_NUM=3DT3.ACCOUNT_MODIFIER_NUM

AND T3.START_DATE<=3D20120515

AND T3.END_DATE>20120515

LEFT OUTER JOIN

( SELECT

Product_Subsection_Id

,MAX(

CASE

WHEN PROD_SUBJ_RELA_TYPE_CD =3D '01' THEN Ledger_Sub= ject_Id

ELSE '00000000'

END

) Ledger_Subject_Id_01

,MAX(

CASE

WHEN PROD_SUBJ_RELA_TYPE_CD =3D '02' THEN Ledger_Sub= ject_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=3DT4.PRODUCT_SUBSECTION_ID

LEFT OUTER JOIN T03_AGREEMENT T5

ON T1.ACCOUNT_NUM=3DT5.ACCOUNT_NUM

AND T1.ACCOUNT_MODIFIER_NUM=3DT5.ACCOUNT_MODIFIER_NUM

LEFT OUTER JOIN T03_AGT_AMOUNT_H T6

ON T1.ACCOUNT_NUM=3DT6.ACCOUNT_NUM

AND T1.ACCOUNT_MODIFIER_NUM=3DT6.ACCOUNT_MODIFIER_NUM

AND T6.AGT_AMT_TYPE_CD IN('001','215')

AND T6.START_DATE<=3D20120515

AND T6.END_DATE>20120515

LEFT OUTER JOIN

(SELECT

Account_Num

,Account_Modifier_Num

,Currency_Cd

,SUM(

CASE

WHEN AGT_AMT_TYPE_CD =3D '001' THEN COALESCE(Agt_Amt= ,0.0)

ELSE 0.0

END

) Agt_Amt_001

,SUM(

CASE

WHEN AGT_AMT_TYPE_CD =3D '003' THEN COALESCE(Agt_Amt= ,0.0)

ELSE 0.0

END

) Agt_Amt_003

,SUM(

CASE

WHEN AGT_AMT_TYPE_CD =3D '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','0= 07')

AND START_DATE<=3D20120515

AND END_DATE> 20120515

GROUP BY Account_Num,Account_Modifier_Num,Currency_Cd

) T7

ON T1.ACCOUNT_NUM=3DT7.ACCOUNT_NUM

AND T1.ACCOUNT_MODIFIER_NUM=3DT7.ACCOUNT_MODIFIER_NUM

LEFT OUTER JOIN T03_AGT_DATE_H T8

ON T1.ACCOUNT_NUM=3DT8.ACCOUNT_NUM

AND T1.ACCOUNT_MODIFIER_NUM=3DT8.ACCOUNT_MODIFIER_NUM

AND T8.Date_Type_Cd=3D'002'

AND T8.START_DATE<=3D20120515

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=3D'01'

AND Agt_Status_Cd NOT IN('102','N03','AA= 2')

AND START_DATE<=3D20120515

AND END_DATE>20120515

)T9

ON T1.ACCOUNT_NUM=3DT9.ACCOUNT_NUM

AND T1.ACCOUNT_MODIFIER_NUM=3DT9.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 <=3D 20120515

AND End_Date > 20120515

AND Agt_Party_Rela_Cd =3D '24'

) T10

ON T1.ACCOUNT_NUM =3D T10.ACCOUNT_NUM

AND T1.ACCOUNT_MODIFIER_NUM =3D 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.=A0
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 a= ny information herein.=A0
If you have received this message in error, please ad= vise the sender immediately by reply email and delete this message. Thank y= ou.



-- =
Nitin Pawar
--047d7bdc10187c3c0a04e8ec76d4--