Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 0E0DD200A01 for ; Tue, 3 May 2016 15:08:05 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id 0D1FB1609F4; Tue, 3 May 2016 15:08:05 +0200 (CEST) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 41DC51609F3 for ; Tue, 3 May 2016 15:08:02 +0200 (CEST) Received: (qmail 38196 invoked by uid 500); 3 May 2016 13:08:00 -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 38186 invoked by uid 99); 3 May 2016 13:08:00 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd3-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 03 May 2016 13:08:00 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd3-us-west.apache.org (ASF Mail Server at spamd3-us-west.apache.org) with ESMTP id 3F5C9180140 for ; Tue, 3 May 2016 13:08:00 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd3-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 2.398 X-Spam-Level: ** X-Spam-Status: No, score=2.398 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=2, KAM_LINEPADDING=1.2, RCVD_IN_DNSWL_LOW=-0.7, RCVD_IN_MSPIKE_H2=-0.001, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd3-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd3-us-west.apache.org [10.40.0.10]) (amavisd-new, port 10024) with ESMTP id hxrBf7PdN1Xl for ; Tue, 3 May 2016 13:07:54 +0000 (UTC) Received: from mail-vk0-f43.google.com (mail-vk0-f43.google.com [209.85.213.43]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with ESMTPS id AD37E5F488 for ; Tue, 3 May 2016 13:07:53 +0000 (UTC) Received: by mail-vk0-f43.google.com with SMTP id o133so22333824vka.0 for ; Tue, 03 May 2016 06:07:53 -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; bh=gzX7fQqpwpQvnePmwKgSXzzmhyEBONIV5sGlRL5Ax/4=; b=gaTil2GI3FxV/cRwQH7aBS8FGGDAqcFtSnai/bnsRpXolN9ESmPTNp4v/KqBduc+4u 2mASgCzgXNsuzBVBB21q88i9USa2dmO4MBu8gysSx7+6VRZvrOqjmrGsRSxo9pguOiEC sECHQrP5L1HMClD6LiU55vBZCeSFSn+q9/2KPAIE7/b88HOfSTcYNsoUTgL68tP8/V6S IIRu6X1PVjyalIsUAs9rn2sTn/ShIQHppySudnKp5tQKwQRcIYxV+AnXvhJ9vOsxS+jL Y6iFt2K6QWyFAQYX0HTaXz3bqmPo/zB06bCjCn8dH4WkW0qBi03dnDUo0uat3tja2mfO dQrA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:date :message-id:subject:from:to; bh=gzX7fQqpwpQvnePmwKgSXzzmhyEBONIV5sGlRL5Ax/4=; b=fi6nEHHr+lmDSPwL/SoahTVRg3K1yfYUahhvJuRV+2ITXK2We6VV5ZpNNF4CoiGKqj NFuQK5Ni6XWJ+jY4rKGRvqXBssGgTwwh5eP1kTkhifm3q5kLkkN1oRFABUp9cllLK51n 43pBN6YRYq5crsXn7nwhpoLqOkzo2guFW6YLHapOb+d01cYOhxkFXwbtYDY1m9192etg eTspsIpbGZME6G6/rNi1FT/lL7qy+GsiAz67WInn0vP+4JUpCouYsY0sfqLOUGPfIsNp bSmKYdQh/YUEQIrRN6x2zJX8GqmgZAJTvyzzfRFWOSjFsOHb+VY7S9W4xBCtuRT1XKja LVbg== X-Gm-Message-State: AOPr4FXVJq/XlicMS6uWYItuC/g61N0LU2YMAuRD18580JyjipQiSGxHPeK50y3uP2dCymBnqlQfXO6v+botxA== MIME-Version: 1.0 X-Received: by 10.176.4.162 with SMTP id 31mr868186uaw.128.1462280873124; Tue, 03 May 2016 06:07:53 -0700 (PDT) Received: by 10.159.38.164 with HTTP; Tue, 3 May 2016 06:07:53 -0700 (PDT) In-Reply-To: References: Date: Tue, 3 May 2016 06:07:53 -0700 Message-ID: Subject: Re: Question on Implementing CASE in Hive Join From: Kishore A To: user@hive.apache.org Content-Type: multipart/alternative; boundary=94eb2c125290012cf10531efcc0a archived-at: Tue, 03 May 2016 13:08:05 -0000 --94eb2c125290012cf10531efcc0a Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Thank you for your reply on this. Your recent query is working perfectly. Yes, we don't have primary key here in this case. Are there some other ways of achieving the same output when we don't have a primary key? On Wed, Apr 27, 2016 at 12:11 PM, Markovitz, Dudu wrote: > In order to achieve the LEFT JOIN I=E2=80=99m generating a primary key to= table A > using ROW_NUMBER. > > If you already have a primary key, you can use it and skip the WITH claus= e. > > > > Dudu > > > > *with* a_rn *as* (*select* row_number () over () *as* rn,* *from* = a) > > > > *select* * > > > > *from* a_rn *as* a > > > > *left* *join* (*select* a.rn > > ,b.* > > > > *from* a_rn *as* a > > > > *cross* *join* b > > > > *where* a.*type* =3D b.*type* > > > > *and* a.code like *case* b.code *= when* 'All' *then* '%' *else* b.code *end* > > *and* a.country like *case* b.country *= when* 'All' *then* '%' *else* b.country *end* > > *and* a.flag like *case* b.flag *= when* 'All' *then* '%' *else* b.flag *end* > > ) > > *as* b > > > > *on* b.rn =3D > > a.rn > > *;* > > > > 1 101 USA OAS Y > 1 101 All OAS All John > > 2 142 US OAS X > 2 142 All OAS All John > > 3 101 US Retail X > 3 All All Retail All John > > 4 142 USA MO X NULL > NULL NULL NULL NULL NULL > > 5 192 US Mod X > 5 192 All Mod All Sean > > 6 101 USA Sal X NULL > NULL NULL NULL NULL NULL > > 7 101 USA Win X NULL > NULL NULL NULL NULL NULL > > 8 101 USA Feed X NULL > NULL NULL NULL NULL NULL > > 9 142 CAN Feed X NULL > NULL NULL NULL NULL NULL > > 10 101 USA OCP X NULL > NULL NULL NULL NULL NULL > > 11 101 USA STORE X NULL NULL > NULL NULL NULL NULL > > 12 129 USA Tax Y NULL > NULL NULL NULL NULL NULL > > 13 119 USA Tax Y NULL > NULL NULL NULL NULL NULL > > 14 101 USA Pus Y NULL > NULL NULL NULL NULL NULL > > 15 142 CN Pus Y > 15 142 All Pus Y Smith > > 16 142 CA Pus Y > 16 142 All Pus Y Smith > > 17 142 US Pus S NULL > NULL NULL NULL NULL NULL > > 18 101 CN Tax Y > 18 101 All Tax Y Smith > > 19 101 CA Tax Y > 19 101 All Tax Y Smith > > 20 101 USA Tax Y 20 > 101 All Tax Y Smith > > 21 101 US Tax X > 21 101 All Tax X John > > > > *From:* Kishore A [mailto:kishore.atmakuri@gmail.com] > *Sent:* Wednesday, April 27, 2016 9:15 PM > *To:* user@hive.apache.org > *Subject:* RE: Question on Implementing CASE in Hive Join > > > > I feel the mail at your end has got a different format. > > Remove * and - in the data what you see...That is my final data....You ca= n > correlate with B > > 2. I need A LeftJoin B > > On Apr 27, 2016 11:41 PM, "Markovitz, Dudu" wrote= : > > Huston, we have a problem J > > > > 1. > > Data > > > > Here are the tables with tabs replaced by =E2=80=98*=E2=80=99 and spaces = replaced by =E2=80=98-=E2=80=99. > > A is a mismatch of tabs and spaces and B contains only (a very strange > number of) spaces. > > > > So you=E2=80=99re using TRIM with all A columns, but how exactly have you= defined > table B? > > > > > > *A* > > 101*---US*------Tax*----X > > 101*---USA*------Tax*----Y > > 101*---CA*------Tax*----Y > > 101*---CN*------Tax*----Y > > 142*---US*------Pus*----S > > 142*---CA*------Pus*----Y > > 142*---CN*------Pus*----Y > > 101*---USA*------Pus*----Y > > 119*---USA*------Tax*----Y > > 129*---USA*------Tax*----Y > > 101*---USA*------STORE*----X > > 101*---USA*------OCP*----X > > 142*---CAN*------Feed*----X > > 101*---USA*------Feed*----X > > 101*---USA*------Win*----X > > 101*---USA*------Sal*----X > > 192*---US*------Mod*----X > > 142*---USA*------MO*----X > > 101*---US*------Retail*X > > 142*---US*------OAS*----X > > 101*---USA*------OAS*----Y > > > > *B* > > All--------All-----------Retail------All----John > > All-------US----------OM----------X------John > > ALL-----US---------OM----------Y------Smith > > 101-----All----------OAS---------All----John > > 142-----All----------OAS---------All----John > > 192-----All----------Mod----------All----Sean > > 101-----All----------Tax-----------X------John > > 101-----All---------Tax------------Y------Smith > > 142-----US---------Pus----------X------John > > 142-----All----------Pus----------Y------Smith-- > > > > > > 2. > > Requirement > > > > =E2=80=9CA left join B=E2=80=9D guarantees that all the records from A ap= pear in the > result set and that the number of rows in the result set is *at least* > the number of rows of A. > > You should take into consideration that a record in A might have several > matches from B. > > > > Also =E2=80=93 > > In your original question you=E2=80=99ve talked about =E2=80=9CB LEFT JOI= N A=E2=80=9D and now it=E2=80=99s > =E2=80=9CA LEFT JOIN B=E2=80=9D. > > > > 3. > > Scenario 1 > > > > I don=E2=80=99t want to start a lecture at that point, but =E2=80=98ON=E2= =80=98 and =E2=80=98WHERE=E2=80=99 have a > completely different meaning and use. > > You=E2=80=99re conditions in the WHERE clause turned the LEFT JOIN to INN= ER JOIN. > > > > 4. > > Scenario 2 > > > > I=E2=80=99ve suggested =E2=80=98LIKE=E2=80=99 not =E2=80=98=3D=E2=80=99 (= but it won=E2=80=99t work because of Hive > limitations that require equality conditions for JOIN). > > > > 5. > > Scenario 3 > > > > CROSS JOIN does not use ON (Hive lets you do that but it not an SQL > standard and it=E2=80=99s actually an INNER JOIN). > > > > 6. > > CASE > > > > CASE is defined by ANSI/ISO and works in Hive the same way it works in HQ= L > the same way it works in any other SQL dialect. > > > > In conclusion - > > The solution I gave you in the previous mails works as expected. > > You should check your data and your tables=E2=80=99 definitions. > > > > Just tell me if you want =E2=80=9CB LEFT JOIN A=E2=80=9D or =E2=80=9CA LE= FT JOIN B=E2=80=9D and I=E2=80=99ll send > you the exact code. > > > > Dudu > > > > *From:* Kishore A [mailto:kishore.atmakuri@gmail.com] > *Sent:* Wednesday, April 27, 2016 6:30 PM > *To:* user@hive.apache.org > *Subject:* Re: Question on Implementing CASE in Hive Join > > > > Hello Dudu, > > > > Thank you for previous reply. I tried all the scenarios but nothing is > working out. I need this condition with with minimal steps like by using > case when. > > > > Please find the below > > > > > > TableA > > Code Country Type Flag > > 101 US Tax X > > 101 USA Tax Y > > 101 CA Tax Y > > 101 CN Tax Y > > 142 US Pus S > > 142 CA Pus Y > > 142 CN Pus Y > > 101 USA Pus Y > > 119 USA Tax Y > > 129 USA Tax Y > > 101 USA STORE X > > 101 USA OCP X > > 142 CAN Feed X > > 101 USA Feed X > > 101 USA Win X > > 101 USA Sal X > > 192 US Mod X > > 142 USA MO X > > 101 US Retail X > > 142 US OAS X > > 101 USA OAS Y > > > > TableB > > Code Country Type Flag Value > > All All Retail All John > > All US OM X John > > ALL US OM Y Smith > > 101 All OAS All John > > 142 All OAS All John > > 192 All Mod All Sean > > 101 All Tax X John > > 101 All Tax Y Smith > > 142 US Pus X John > > 142 All Pus Y Smith > > > > > > This is how my data in reality looks like. *Type *column is the fixed > column here > > > > Requirement : TableA LeftJoin TableB , stamp the matching *Value *from > TableB. Total result should have the number of rows as TableA > > > > Scenario 1: > > > > Select * FROM TableA a > > Left Join TableB b ON > > LOWER(TRIM(a.Type)) =3D LOWER(TRIM(b.Type)) > > WHERE LOWER(TRIM(a.Code)) =3D (CASE WHEN LOWER(TRIM(b.Code)) =3D > LOWER('All') THEN LOWER(TRIM(a.Code)) ELSE LOWER(TRIM(b.Code)) END) > > AND LOWER(TRIM(a.country)) =3D (CASE WHEN LOWER(TRIM(b.country)) =3D > LOWER('All') THEN LOWER(TRIM(a.country)) ELSE LOWER(TRIM(b.country)) END) > > AND LOWER(TRIM(a.flag)) =3D (CASE WHEN LOWER(TRIM(b.flag)) =3D > LOWER('All') THEN LOWER(TRIM(a.flag)) ELSE LOWER(TRIM(b.flag)) END); > > > > on the output the number of rows should be same as the left table, but as > I am apply WHERE data is getting filtered out and is giving only > > matching records > > > > > > > > Scenario 2: Suggested by you > > > > Select * FROM TableA a > > Left Join TableB b ON > > LOWER(TRIM(a.Type)) =3D LOWER(TRIM(b.Type)) > > AND LOWER(TRIM(a.Code)) =3D (CASE WHEN LOWER(TRIM(b.Code)) =3D > LOWER('All') THEN '%' ELSE LOWER(TRIM(b.Code)) END) > > AND LOWER(TRIM(a.country)) =3D (CASE WHEN LOWER(TRIM(b.country)) =3D > LOWER('All') THEN '%' ELSE LOWER(TRIM(b.country)) END) > > AND LOWER(TRIM(a.flag)) =3D (CASE WHEN LOWER(TRIM(b.flag)) =3D > LOWER('All') THEN '%' ELSE LOWER(TRIM(b.flag)) END); > > > > As per the left join this is giving me the number of rows just as the lef= t > side table but I am not having the matching records as per the > > right table. I have NULLs on the right side. > > > > Scenario 3: Cross Join > > > > Select * FROM TableA a > > CROSS Join TableB b ON > > LOWER(TRIM(a.Type)) =3D LOWER(TRIM(b.Type)) > > AND LOWER(TRIM(a.Code)) =3D (CASE WHEN LOWER(TRIM(b.Code)) =3D > LOWER('All') THEN '%' ELSE LOWER(TRIM(b.Code)) END) > > AND LOWER(TRIM(a.country)) =3D (CASE WHEN LOWER(TRIM(b.country)) =3D > LOWER('All') THEN '%' ELSE LOWER(TRIM(b.country)) END) > > AND LOWER(TRIM(a.flag)) =3D (CASE WHEN LOWER(TRIM(b.flag)) =3D > LOWER('All') THEN '%' ELSE LOWER(TRIM(b.flag)) END); > > > > Giving same result as Scenario 2 - All the left columns with NULLs on rig= ht > > > > I don't know if we still need to fine tune the query as Hive is not > supporting CASE WHEN just as in SQL. Your thoughts are more helpful to me= . > > > > Kishore > > > > > > On Wed, Apr 20, 2016 at 12:04 PM, Markovitz, Dudu > wrote: > > The second version works as expected (after fixing a typo in the word > =E2=80=98indicator=E2=80=99). > > If you don=E2=80=99t get any results you should check your data (maybe th= e fields > contains trailing spaces or control characters etc.). > > > > If you=E2=80=99re willing to replace the =E2=80=98OUTER=E2=80=99 with =E2= =80=98INNER=E2=80=99, there=E2=80=99s another > option - > > > > *select* * > > > > *from* b > > > > *cross* *join* a > > > > *where* a.*type* =3D b.*type* > > *and* a.code like *case* b.code *when* 'ALL' *then* '%' > *else* b.code *end* > > *and* a.indicator like *case* b.indicator *when* 'ALL' *then* '%' > *else* b.indicator *end* > > *;* > > > > Dudu > > > > > > *From:* Kishore A [mailto:kishore.atmakuri@gmail.com] > *Sent:* Wednesday, April 20, 2016 5:04 PM > > > *To:* user@hive.apache.org > *Subject:* Re: Question on Implementing CASE in Hive Join > > > > Hi Dudu, > > > > Thank you for sending queries around this. > > > > I have run these queries and below are the observations > > > > 1. It did return the same error as before" SemanticException [Error > 10017]: Line 4:4 Both left and right aliases encountered in JOIN 'code'" > > > > 2. Query execution is successful but not retrieving any results out of it= . > > > > I am clueless and not able to proceed to next step until this is resolved= . > Do you have any other suggestions please? > > > > Kishore > > > > On Tue, Apr 19, 2016 at 6:08 AM, Markovitz, Dudu > wrote: > > Please try the following two options. > > Option 2 might be better, performance wise (depending of the data volume > and characteristics). > > > > P.s. > > I didn=E2=80=99t understand the explanation about the LEFT JOIN > > > > > > Dudu > > > > 1. > > > > *select* b.code > > ,b.*value* > > > > *from* b > > > > *left* *join* a > > > > *on* a.*type* =3D b.*type* > > *and* a.code like *case* b.code *when* > 'ALL' *then* '%' *else* b.code *end* > > *and* a.indicator like *case* b.indicatior *when* > 'ALL' *then* '%' *else* b.indicatior *end* > > *;* > > > > > > > > 2. > > > > *select* b.code > > ,b.*value* > > > > *from* b > > > > *left* *join* a > > > > *on* a.*type* =3D b.*type* > > *and* a.code =3D b.code > > *and* a.indicator =3D b.indicatior > > > > *where* b.code !=3D 'ALL' > > *and* b.indicatior !=3D 'ALL' > > > > *union* *all* > > > > *select* b.code > > ,b.*value* > > > > *from* b > > > > *left* *join* a > > > > *on* a.*type* =3D b.*type* > > *and* a.indicator =3D b.indicatior > > > > *where* b.code =3D 'ALL' > > *and* b.indicatior !=3D 'ALL' > > > > *union* *all* > > > > *select* b.code > > ,b.*value* > > > > *from* b > > > > *left* *join* a > > > > *on* a.*type* =3D b.*type* > > *and* a.code =3D b.code > > > > *where* b.code !=3D 'ALL' > > *and* b.indicatior =3D 'ALL' > > > > *union* *all* > > > > *select* b.code > > ,b.*value* > > > > *from* b > > > > *left* *join* a > > > > *on* a.*type* =3D b.*type* > > > > *where* b.code =3D 'ALL' > > *and* b.indicatior =3D 'ALL' > > *;* > > > > > > *From:* Kishore A [mailto:kishore.atmakuri@gmail.com] > *Sent:* Tuesday, April 19, 2016 3:51 PM > *To:* user@hive.apache.org > *Subject:* Re: Question on Implementing CASE in Hive Join > > > > Hi Dudu, > > > > Actually we use both fields from left and right tables, I mentioned right > table just for my convenience to check whether ALL from right table can b= e > pulled as per join condition match. > > > > One more reason why we use left join is we should not have extra columns > after join. > > > > Kishore > > > > > > > > On Tue, Apr 19, 2016 at 5:46 AM, Markovitz, Dudu > wrote: > > Before dealing with the technical aspect, can you please explain what is > the point of using LEFT JOIN without selecting any field from table A? > > > > Thanks > > > > Dudu > > > > *From:* Kishore A [mailto:kishore.atmakuri@gmail.com] > *Sent:* Tuesday, April 19, 2016 2:29 PM > *To:* user@hive.apache.org > *Subject:* Question on Implementing CASE in Hive Join > > > > Hi, > > > > I have a scenario to implement to cases in Hive Joins. I need to implemen= t > case on the value on which join condition to be applied. > > > > Table A > > Code// Type// Indicator// Value// > > A 1 XYZ John > > B 1 PQR Smith > > C 2 XYZ John > > C 2 PQR Smith > > D 3 PQR Smith > > E 3 XYZ Smith > > F 4 MNO Smith > > G 3 MNO Smith > > ... > > --94eb2c125290012cf10531efcc0a Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Thank you for your reply on this.=C2=A0

Your recent query is working perfectly.=C2=A0

Yes= , we don't have primary key here in this case. Are there some other way= s of achieving the same output when we don't have a primary key?
<= /div>

On Wed, Apr = 27, 2016 at 12:11 PM, Markovitz, Dudu <dmarkovitz@paypal.com> wrote:

In order to achieve the LEFT JOIN I= =E2=80=99m generating a primary key to table A using ROW_NUMBER.<= /u>

If you already have a primary key, yo= u can use it and skip the WITH clause.

=C2=A0

Dudu

=C2=A0

with=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 a_rn as (select row_number () over () as=
 rn,=
* from a)=
 
=C2=A0
select=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 *
=C2=A0
from=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 a_rn as a
=C2=A0
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0 left join=C2=A0 (select=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0 a.rn=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0,b.*
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0 
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0from=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 a_rn <=
/span>as a=
 
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0cross join=C2=A0 b 
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0where=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 a.=
type=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =3D=
 b.type=

=C2=A0
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 and =
a.code=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 like case b.code<=
/span>=C2=A0=C2=A0=C2=A0 when 'All' then '%' else b.code=C2=A0=C2=A0=C2=A0=C2=A0 end=C2=A0 =

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
and a.country=C2=A0=C2=A0 like case b.country when<=
/b> 'A=
ll' then '%' else b.country=C2=A0 end 
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
and a.flag=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 like case<=
/span> =
b.fl=
ag=C2=A0=C2=A0=C2=A0 when 'All' then '%' else b.flag=C2=A0=C2=A0=C2=A0=C2=A0 end 
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0)=C2=A0=C2=A0 
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0as b=

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0 
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0on=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0 b.rn =3D
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0a.rn=C2=A0 
;=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0

=C2=A0

1=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 101=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 USA=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 OAS=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 Y=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 1=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 All=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 OAS=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 All=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 John

2=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 142=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 US=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 OAS=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 X=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 2=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 142=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 All=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 OAS=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 All=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 John<= /p>

3=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 101=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 US=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 Retail=C2=A0=C2=A0=C2=A0=C2=A0 X=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 3=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 All=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 All=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 Retail=C2=A0=C2=A0=C2=A0=C2=A0 All=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 John

4=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 142=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 USA=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 MO=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 X=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0= =C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2= =A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL

5=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 192=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 US=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 Mod=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 X=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 5=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 192=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 All=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 Mod=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 All=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Sean

6=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 101=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 USA=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Sal=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 X=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 = NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0= =C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL

7=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 101=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 USA=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Win=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 X=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0= =C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2= =A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL

8=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 101=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 USA=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Feed=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 X=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0= =C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0= =C2=A0=C2=A0=C2=A0 NULL

9=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 142=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 CAN=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Feed=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 X=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0= =C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0= =C2=A0=C2=A0=C2=A0 NULL

10=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 USA=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 OCP=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 X=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL= =C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2= =A0=C2=A0 NULL

11=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 USA=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 STORE=C2=A0=C2=A0 X=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0= =C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2= =A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL

12=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 129=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 USA=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Tax=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 Y=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2= =A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 N= ULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL

13=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 119=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 USA=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Tax=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 Y=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2= =A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 N= ULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL

14=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 USA=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Pus=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 Y=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2= =A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 N= ULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL

15=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 142=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 CN=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Pus=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Y=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 15=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 142=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 All=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 Pus=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Y=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Smith

16=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 142=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 CA=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Pus=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Y=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 16=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 142=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 All=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 Pus=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Y=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Smith

17=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 142=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 US=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Pus=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 S=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2= =A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL=C2=A0=C2= =A0=C2=A0=C2=A0 NULL=C2=A0=C2=A0=C2=A0=C2=A0 NULL

18=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 CN=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Tax=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Y=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 18=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 All=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 Tax=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Y=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Smith

19=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 CA=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Tax=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Y=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 19=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 All=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 Tax=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Y=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Smith

20=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 USA=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Tax=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 Y=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 20=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 All= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Tax=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Y=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Smith

21=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 US=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Tax=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 X=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 21=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 All=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 T= ax=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 X=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 John

=C2=A0

From: Kishore A [mailto:kishore.atmakuri@gmail.= com]
Sent: Wednesday, April 27, 2016 9:15 PM
To: user@h= ive.apache.org
Subject: RE: Question on Implementing CASE in Hive Join

=C2=A0

I feel the mail at your end has got a different format.

Remove * and - in the data what you see...That is my final data....You c= an correlate with B

2. I need A LeftJoin B

On Apr 27, 2016 11:41 PM, "Markovitz, Dudu"= ; <dmarkovitz= @paypal.com> wrote:

Huston, we have a problem J

=C2=A0

1.

Data

=C2=A0

Here are the tables with tabs replace= d by =E2=80=98*=E2=80=99 and spaces replaced by =E2=80=98-=E2=80=99.=

A is a mismatch of tabs and spaces an= d B contains only (a very strange number of) spaces.

=C2=A0

So you=E2=80=99re using TRIM with all= A columns, but how exactly have you defined table B?<= /p>

=C2=A0

=C2=A0

A<= /u>

101*---US*------Tax*----X

101*---USA*------Tax*----Y<= /u>

101*---CA*------Tax*----Y

101*---CN*------Tax*----Y

142*---US*------Pus*----S

142*---CA*------Pus*----Y

142*---CN*------Pus*----Y

101*---USA*------Pus*----Y<= /u>

119*---USA*------Tax*----Y<= /u>

129*---USA*------Tax*----Y<= /u>

101*---USA*------STORE*----X

101*---USA*------OCP*----X<= /u>

142*---CAN*------Feed*----X=

101*---USA*------Feed*----X=

101*---USA*------Win*----X<= /u>

101*---USA*------Sal*----X<= /u>

192*---US*------Mod*----X

142*---USA*------MO*----X

101*---US*------Retail*X

142*---US*------OAS*----X

101*---USA*------OAS*----Y<= /u>

=C2=A0

B<= /u>

All--------All-----------Retail------= All----John

All-------US----------OM----------X--= ----John

ALL-----US---------OM----------Y-----= -Smith

101-----All----------OAS---------All-= ---John

142-----All----------OAS---------All-= ---John

192-----All----------Mod----------All= ----Sean

101-----All----------Tax-----------X-= -----John

101-----All---------Tax------------Y-= -----Smith

142-----US---------Pus----------X----= --John

142-----All----------Pus----------Y--= ----Smith--

=C2=A0

=C2=A0

2.

Requirement

=C2=A0

=E2=80=9CA left join B=E2=80=9D guara= ntees that all the records from A appear in the result set and that the num= ber of rows in the result set is at least the number of rows of = A.

You should take into consideration th= at a record in A might have several matches from B.

=C2=A0

Also =E2=80=93

In your original question you=E2=80= =99ve talked about =E2=80=9CB LEFT JOIN A=E2=80=9D and now it=E2=80=99s =E2= =80=9CA LEFT JOIN B=E2=80=9D.

=C2=A0

3.

Scenario =C2=A01

=C2=A0

I don=E2=80=99t want to start a lectu= re at that point, but =E2=80=98ON=E2=80=98 and =E2=80=98WHERE=E2=80=99 have= a completely different meaning and use.

You=E2=80=99re conditions in the WHER= E clause turned the LEFT JOIN to INNER JOIN.

=C2=A0

4.

Scenario 2

=C2=A0

I=E2=80=99ve suggested =E2=80=98LIKE= =E2=80=99 not =E2=80=98=3D=E2=80=99 (but it won=E2=80=99t work because of H= ive limitations that require equality conditions for JOIN).

=C2=A0

5.

Scenario 3

=C2=A0

CROSS JOIN does not use ON (Hive lets= you do that but it not an SQL standard and it=E2=80=99s actually an INNER JOIN).

=C2=A0

6.

CASE

=C2=A0

CASE is defined by ANSI/ISO and works= in Hive the same way it works in HQL the same way it works in any other SQL dialect.

=C2=A0

In conclusion -<= /p>

The solution I gave you in the previo= us mails works as expected.

You should check your data and your t= ables=E2=80=99 definitions.

=C2=A0

Just tell me if you want =E2=80=9CB L= EFT JOIN A=E2=80=9D or =E2=80=9CA LEFT JOIN B=E2=80=9D and I=E2=80=99ll sen= d you the exact code.

=C2=A0

Dudu

=C2=A0

From: Kishore A [mailto:kishore.atmakuri@gmail.= com]
Sent: Wednesday, April 27, 2016 6:30 PM
To: user@h= ive.apache.org
Subject: Re: Question on Implementing CASE in Hive Join

=C2=A0

Hello Dudu,

=C2=A0

Thank you for previous reply. I tried all the scenar= ios but nothing is working out. I need this condition with with minimal ste= ps like by using case when.

=C2=A0

Please find the below

=C2=A0

=C2=A0

TableA

Code =C2=A0 Country =C2=A0 =C2=A0Type =C2=A0 =C2=A0 = Flag

101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 US=C2=A0=C2= =A0=C2=A0 =C2=A0 =C2=A0 =C2=A0Tax=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0X

101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 USA =C2=A0 = =C2=A0 =C2=A0Tax=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 =C2=A0 =C2=A0Y

101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 CA=C2=A0=C2= =A0 =C2=A0 =C2=A0 =C2=A0Tax=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0Y

101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 CN=C2=A0=C2= =A0 =C2=A0 =C2=A0 =C2=A0Tax=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0Y

142=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 US=C2=A0=C2= =A0=C2=A0 =C2=A0 =C2=A0 =C2=A0Pus =C2=A0 =C2=A0S

142=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 CA=C2=A0=C2= =A0 =C2=A0 =C2=A0 =C2=A0Pus =C2=A0 =C2=A0Y

142=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 CN=C2=A0=C2= =A0 =C2=A0 =C2=A0 =C2=A0Pus =C2=A0 =C2=A0Y

101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 USA =C2=A0 = =C2=A0 =C2=A0Pus =C2=A0 =C2=A0Y

119=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 USA =C2=A0 = =C2=A0 =C2=A0Tax=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 =C2=A0 =C2=A0Y

129=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 USA =C2=A0 = =C2=A0 =C2=A0Tax=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 =C2=A0 =C2=A0Y

101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 USA =C2=A0 = =C2=A0 =C2=A0STORE=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0X

101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 USA =C2=A0 = =C2=A0 =C2=A0OCP=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2= =A0 =C2=A0X

142=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 CAN =C2=A0 = =C2=A0 =C2=A0Feed=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2= =A0 =C2=A0X

101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 USA =C2=A0 = =C2=A0 =C2=A0Feed=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2= =A0 =C2=A0X

101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 USA =C2=A0 = =C2=A0 =C2=A0Win=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 =C2=A0 =C2=A0X

101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 USA =C2=A0 = =C2=A0 =C2=A0Sal =C2=A0 =C2=A0X

192=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 US=C2=A0=C2= =A0=C2=A0 =C2=A0 =C2=A0 =C2=A0Mod=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 =C2=A0 =C2=A0X

142=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 USA =C2=A0 = =C2=A0 =C2=A0MO=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 =C2=A0 =C2=A0X

101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 US=C2=A0=C2= =A0=C2=A0 =C2=A0 =C2=A0 =C2=A0Retail=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 X

142=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 US=C2=A0=C2= =A0=C2=A0 =C2=A0 =C2=A0 =C2=A0OAS=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 =C2=A0 =C2=A0X

101=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 USA =C2=A0 = =C2=A0 =C2=A0OAS=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2= =A0 =C2=A0Y

=C2=A0

TableB

Code =C2=A0 Country =C2=A0 =C2=A0Type =C2=A0 =C2=A0 = Flag =C2=A0 Value

All =C2=A0 =C2=A0 =C2=A0 =C2=A0All =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 Retail =C2=A0 =C2=A0 =C2=A0All =C2=A0 =C2=A0John

All =C2=A0 =C2=A0 =C2=A0 US =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0OM =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0X =C2=A0 =C2=A0 =C2=A0John

ALL =C2=A0 =C2=A0 US =C2=A0 =C2=A0 =C2=A0 =C2=A0 OM = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Y =C2=A0 =C2=A0 =C2=A0Smith=

101 =C2=A0 =C2=A0 All =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0OAS =C2=A0 =C2=A0 =C2=A0 =C2=A0 All =C2=A0 =C2=A0John

142 =C2=A0 =C2=A0 All =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0OAS =C2=A0 =C2=A0 =C2=A0 =C2=A0 All =C2=A0 =C2=A0John

192 =C2=A0 =C2=A0 All =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Mod =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0All =C2=A0 =C2=A0Sean=

101 =C2=A0 =C2=A0 All =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Tax =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 X =C2=A0 =C2=A0 =C2=A0John<= /u>

101 =C2=A0 =C2=A0 All =C2=A0 =C2=A0 =C2=A0 =C2=A0 Ta= x =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Y =C2=A0 =C2=A0 =C2=A0Smith

142 =C2=A0 =C2=A0 US =C2=A0 =C2=A0 =C2=A0 =C2=A0 Pus= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0X =C2=A0 =C2=A0 =C2=A0John=

142 =C2=A0 =C2=A0 All =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Pus =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Y =C2=A0 =C2=A0 =C2=A0Smith =C2= =A0

=C2=A0

=C2=A0

This is how my data in reality looks like. Type column is the fixed column here

=C2=A0

Requirement : TableA LeftJoin TableB , stamp the mat= ching Value from TableB. Total result should have the number of rows as Ta= bleA

=C2=A0

Scenario =C2=A01:

=C2=A0

Select * FROM TableA a

=C2=A0 =C2=A0 Left Join TableB b ON

=C2=A0 =C2=A0 LOWER(TRIM(a.Type)) =3D LOWER(TRIM(b.T= ype))=C2=A0

=C2=A0 =C2=A0 WHERE LOWER(TRIM(a.Code)) =3D (CASE WH= EN LOWER(TRIM(b.Code)) =3D LOWER('All') THEN LOWER(TRIM(a.Code)) EL= SE LOWER(TRIM(b.Code)) END)

=C2=A0 =C2=A0 AND LOWER(TRIM(a.country)) =3D (CASE W= HEN LOWER(TRIM(b.country)) =3D LOWER('All') THEN LOWER(TRIM(a.count= ry)) ELSE LOWER(TRIM(b.country)) END)

=C2=A0 =C2=A0 AND LOWER(TRIM(a.flag)) =C2=A0=3D (CAS= E WHEN LOWER(TRIM(b.flag)) =3D LOWER('All') THEN LOWER(TRIM(a.flag)= ) ELSE LOWER(TRIM(b.flag)) END);

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0

on the output the number of rows should be same as t= he left table, but as I am apply WHERE data is getting filtered out and is = giving only=C2=A0

matching records

=C2=A0

=C2=A0

=C2=A0

Scenario 2: Suggested by you

=C2=A0

Select * FROM TableA a

=C2=A0 =C2=A0 Left Join TableB b ON

=C2=A0 =C2=A0 LOWER(TRIM(a.Type)) =3D LOWER(TRIM(b.T= ype))=C2=A0

=C2=A0 =C2=A0 AND LOWER(TRIM(a.Code)) =3D (CASE WHEN= LOWER(TRIM(b.Code)) =3D LOWER('All') THEN '%' ELSE LOWER(T= RIM(b.Code)) END)

=C2=A0 =C2=A0 AND LOWER(TRIM(a.country)) =3D (CASE W= HEN LOWER(TRIM(b.country)) =3D LOWER('All') THEN '%' ELSE L= OWER(TRIM(b.country)) END)

=C2=A0 =C2=A0 AND LOWER(TRIM(a.flag)) =C2=A0=3D (CAS= E WHEN LOWER(TRIM(b.flag)) =3D LOWER('All') THEN '%'=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ELSE LOWER(TRI= M(b.flag)) END);

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0

As per the left join this is giving me the number of= rows just as the left side table but I am not having the matching records = as per the

right table. I have NULLs on the right side.<= u>

=C2=A0

Scenario 3: Cross Join=C2=A0

=C2=A0

Select * FROM TableA a

=C2=A0 =C2=A0 CROSS Join TableB b ON

=C2=A0 =C2=A0 LOWER(TRIM(a.Type)) =3D LOWER(TRIM(b.T= ype))=C2=A0

=C2=A0 =C2=A0 AND LOWER(TRIM(a.Code)) =3D (CASE WHEN= LOWER(TRIM(b.Code)) =3D LOWER('All') THEN '%' ELSE LOWER(T= RIM(b.Code)) END)

=C2=A0 =C2=A0 AND LOWER(TRIM(a.country)) =3D (CASE W= HEN LOWER(TRIM(b.country)) =3D LOWER('All') THEN '%' ELSE L= OWER(TRIM(b.country)) END)

=C2=A0 =C2=A0 AND LOWER(TRIM(a.flag)) =C2=A0=3D (CAS= E WHEN LOWER(TRIM(b.flag)) =3D LOWER('All') THEN '%'=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ELSE LOWER(TRI= M(b.flag)) END);

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0

Giving same result as Scenario 2 - All the left colu= mns with NULLs on right

=C2=A0

I don't know if we still need to fine tune the q= uery as Hive is not supporting CASE WHEN just as in SQL. Your thoughts are = more helpful to me.

=C2=A0

Kishore

=C2=A0

=C2=A0

On Wed, Apr 20, 2016 at 12:04 PM, Markovitz, Dudu &l= t;dmarkovitz@pay= pal.com> wrote:

The second version works as expected = (after fixing a typo in the word =E2=80=98indicator=E2=80=99).

If you don=E2=80=99t get any results = you should check your data (maybe the fields contains trailing spaces or control characters etc.).

=C2=A0

If you=E2=80=99re willing to replace = the =E2=80=98OUTER=E2=80=99 with =E2=80=98INNER=E2=80=99, there=E2=80=99s a= nother option -

=C2=A0

select=C2=A0=C2=A0=C2=A0 = =C2=A0=C2=A0*

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0

from=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 b

=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 cross join=C2=A0 a

=C2=A0

where=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 a.type=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 =3D b.type

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 and a.code=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 like case b.code=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 when 'ALL' then '%' else b.code=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 end=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0and a.indicator like case b.indicator when 'ALL' then '%' else b.indicator end

;=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0

=C2=A0

Dudu

=C2=A0

=C2=A0

From: Kishore A [mailto:kishore.atmakuri@gmail.= com]
Sent: Wednesday, April 20, 2016 5:04 PM


To: user@h= ive.apache.org
Subject: Re: Question on Implementing CASE in Hive Join

=C2=A0

Hi Dudu,

=C2=A0

Thank you for sending queries around this.=

=C2=A0

I have run these queries and below are the observati= ons

=C2=A0

1. It did return the same error as before"=C2= =A0SemanticException [Error 10017]: Line 4:4 Both left and right aliases en= countered in JOIN 'code'"

=C2=A0

2. Query execution is successful but not retrieving = any results out of it.

=C2=A0

I am clueless and not able to proceed to next step u= ntil this is resolved. Do you have any other suggestions please?<= /u>

=C2=A0

Kishore

=C2=A0

On Tue, Apr 19, 2016 at 6:08 AM, Markovitz, Dudu <= ;dmarkovitz@payp= al.com> wrote:

Please try the following two options.=

Option 2 might be better, performance= wise (depending of the data volume and characteristics).<= /u>

=C2=A0

P.s.

I didn=E2=80=99t understand the expla= nation about the LEFT JOIN

=C2=A0

=C2=A0

Dudu

=C2=A0

1.

=C2=A0

select=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 b.code

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 ,b.value

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0

from=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 b

=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 left join=C2=A0=C2=A0 a

=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 on=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 a.type=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 =3D b.type

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 and a.code=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 like case b.code=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 when 'ALL' then '%' else b.code=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 end=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= and a.indicator like case b.indicatior when 'ALL' then '%' else b.indicatior end

;

=C2=A0

=C2=A0

=C2=A0

2.

=C2=A0

select=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 b.code

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 ,b.value

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0

from=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 b

=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 left join=C2=A0=C2=A0 a

=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 on=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 a.type=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 =3D b.type

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 and a.code=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 =3D b.code

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 and a.indicator =3D b.indicatior

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=

where=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 b.code=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 !=3D 'ALL'

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 and b.indicatior !=3D 'ALL'

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0

union all

=C2=A0

select=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 b.code

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 ,b.value

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0

from=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 b

=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 left join=C2=A0=C2=A0 a

=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 on=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 a.type=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 =3D b.type

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 and a.indicator =3D b.indicatior

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=

where=C2=A0 =C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0b.code= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =3D 'ALL'

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 and b.indicatior !=3D 'ALL'=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0

union all

=C2=A0

select=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 b.code

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 ,b.value

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0

from=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 b

=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 left join=C2=A0=C2=A0 a

=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 on=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 a.type=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 =3D b.type

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 and a.code=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 =3D b.code

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0

where=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 b.code=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 !=3D 'ALL'

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 and b.indicatior=C2=A0 =3D 'ALL'

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0

union all

=C2=A0

select=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 b.code

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 ,b.value

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0

from=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 b

=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 left join=C2=A0=C2=A0 a

=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 on=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 a.type=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 =3D b.type

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0

where=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 b.code=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 =3D 'ALL'

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 and b.indicatior =3D 'ALL'=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0

;=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0

=C2=A0

=C2=A0

From: Kishore A [mailto:kishore.atmakuri@gmail.= com]
Sent: Tuesday, April 19, 2016 3:51 PM
To: user@h= ive.apache.org
Subject: Re: Question on Implementing CASE in Hive Join

=C2=A0

Hi Dudu,

=C2=A0

Actually we use both fields from left and right tabl= es, I mentioned right table just for my convenience to check whether ALL fr= om right table can be pulled as per join condition match.

=C2=A0

One more reason why we use left join is we should no= t have extra columns after join.

=C2=A0

Kishore

=C2=A0

=C2=A0

=C2=A0

On Tue, Apr 19, 2016 at 5:46 AM, Markovitz, Dudu <= ;dmarkovitz@payp= al.com> wrote:

Before dealing with the technical asp= ect, can you please explain what is the point of using LEFT JOIN without selecting any field from table A?

=C2=A0

Thanks

=C2=A0

Dudu

=C2=A0

From: Kishore A [mailto:kishore.atmakuri@gmail.= com]
Sent: Tuesday, April 19, 2016 2:29 PM
To: user@h= ive.apache.org
Subject: Question on Implementing CASE in Hive Join=

=C2=A0

Hi,

=C2=A0

I have a scenario to implement to cases in Hive Join= s. I need to implement case on the value on which join condition to be appl= ied.

=C2=A0

Table A

Code// Type// Indicator// Value//

A =C2=A0 =C2=A0 =C2=A01 =C2=A0 =C2=A0 =C2=A0XYZ =C2= =A0 =C2=A0 =C2=A0 =C2=A0 John

B =C2=A0 =C2=A0 =C2=A01 =C2=A0 =C2=A0 =C2=A0PQR =C2= =A0 =C2=A0 =C2=A0 =C2=A0 Smith

C =C2=A0 =C2=A0 =C2=A02 =C2=A0 =C2=A0 =C2=A0XYZ =C2= =A0 =C2=A0 =C2=A0 =C2=A0 John

C =C2=A0 =C2=A0 =C2=A02 =C2=A0 =C2=A0 =C2=A0PQR =C2= =A0 =C2=A0 =C2=A0 =C2=A0 Smith

D =C2=A0 =C2=A0 =C2=A03 =C2=A0 =C2=A0 =C2=A0PQR =C2= =A0 =C2=A0 =C2=A0 =C2=A0 Smith

E =C2=A0 =C2=A0 =C2=A03 =C2=A0 =C2=A0 =C2=A0XYZ =C2= =A0 =C2=A0 =C2=A0 =C2=A0 Smith

F =C2=A0 =C2=A0 =C2=A04 =C2=A0 =C2=A0 =C2=A0MNO =C2= =A0 =C2=A0 =C2=A0 =C2=A0 Smith

G =C2=A0 =C2=A0 =C2=A03 =C2=A0 =C2=A0 =C2=A0MNO =C2= =A0 =C2=A0 =C2=A0 =C2=A0 Smith

...


--94eb2c125290012cf10531efcc0a--