Return-Path: X-Original-To: apmail-hadoop-mapreduce-user-archive@minotaur.apache.org Delivered-To: apmail-hadoop-mapreduce-user-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 3D38810364 for ; Mon, 22 Apr 2013 09:20:16 +0000 (UTC) Received: (qmail 48443 invoked by uid 500); 22 Apr 2013 09:20:09 -0000 Delivered-To: apmail-hadoop-mapreduce-user-archive@hadoop.apache.org Received: (qmail 48072 invoked by uid 500); 22 Apr 2013 09:20:09 -0000 Mailing-List: contact user-help@hadoop.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hadoop.apache.org Delivered-To: mailing list user@hadoop.apache.org Received: (qmail 48043 invoked by uid 99); 22 Apr 2013 09:20:08 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 22 Apr 2013 09:20:08 +0000 X-ASF-Spam-Status: No, hits=0.7 required=5.0 tests=ASF_LIST_OPS,HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS,UPPERCASE_75_100 X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of suneel.bigdata@gmail.com designates 209.85.212.174 as permitted sender) Received: from [209.85.212.174] (HELO mail-wi0-f174.google.com) (209.85.212.174) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 22 Apr 2013 09:20:03 +0000 Received: by mail-wi0-f174.google.com with SMTP id m6so3935324wiv.7 for ; Mon, 22 Apr 2013 02:19:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:x-received:date:message-id:subject:from:to :content-type; bh=axm3Q1Su7MEJ2RE7+b3PGGcxCURaXtkwCtBQsAyr6I0=; b=H/f8535i+9/76Ha2lO4bkaeRa4CroKp0gvhPGAKdKuyJWgrT8yeedAeLLVnfjbG5Yb qBZTU0Gx2RW0rah/8NF9wIvwaCCk7P39ns0aAS/lGB1H/wAeaxOr6hFdBuCrDJ3mblwe y3siyCrMdR/HeUVjLArFKciC9TNMo0TmPJ6jTuKL3NDga7FCFySAN9YVObj+I0pi8oOl oaq61PmE8F5eA18V7z7gvGwtD+f7pITtrat9tOi0Q+pGWflG5DjpnBWnCDwYnGpE7qLV k7hi3LVu+V/mT9jHX5ySF8JeuERa+hqH5AprFMxRLmV3TfkEq2r3yDCnVMHKZNuytDha qTsQ== MIME-Version: 1.0 X-Received: by 10.194.109.35 with SMTP id hp3mr50412218wjb.15.1366622382264; Mon, 22 Apr 2013 02:19:42 -0700 (PDT) Received: by 10.194.249.161 with HTTP; Mon, 22 Apr 2013 02:19:42 -0700 (PDT) Date: Mon, 22 Apr 2013 14:49:42 +0530 Message-ID: Subject: SQL to pig latin From: suneel hadoop To: user , "user@hive.apache.org" , user-help@pig.apache.org Content-Type: multipart/alternative; boundary=089e010d8574a314a704daef9188 X-Virus-Checked: Checked by ClamAV on apache.org --089e010d8574a314a704daef9188 Content-Type: text/plain; charset=ISO-8859-1 ---------- Forwarded message ---------- From: *suneel hadoop* Date: Monday, April 22, 2013 Subject: To: user , user-help@pig.apache.org, " user@hive.apache.org" Can any one help me to change this SQL to pig Latin SELECT ('CSS'||DB.DISTRICT_CODE||DB.BILLING_ACCOUNT_NO) BAC_KEY, CASE WHEN T1.TAC_142 IS NULL THEN 'N' ELSE T1.TAC_142 END TAC_142 FROM ( SELECT DISTRICT_CODE,BILLING_ACCOUNT_NO, MAX(CASE WHEN TAC_1 = 'Y' AND (TAC_2 = 'Y' OR TAC_3 = 'Y') THEN 'Y' ELSE 'N' END) TAC_142 FROM ( SELECT DI.DISTRICT_CODE,DI.BILLING_ACCOUNT_NO,DI.INST_SEQUENCE_NO, MAX(CASE WHEN TRIM(DIP.PRODUCT_CODE) = 'A14493' AND UPPER(DI.HAZARD) LIKE '%999%EMERGENCY%LINE%' AND UPPER(DI.WARNING) LIKE '%USE%999%ALERT%METHOD%' THEN 'Y' ELSE 'N' END) TAC_1, MAX(CASE WHEN TRIM(DIP.PRODUCT_TYPE) IN ('20','21') AND TRIM(DIP.MAINTENANCE_CONTRACT) IN ('E','T') THEN 'Y' ELSE 'N' END) TAC_2, MAX(CASE WHEN TRIM(DIP.PRODUCT_CODE) IN ('A14498','A14428','A22640') THEN 'Y' ELSE 'N' END) TAC_3 FROM D_INSTALLATION DI, D_INSTALLATION_PRODUCT DIP WHERE DIP.INST_SEQUENCE_NO = DI.INST_SEQUENCE_NO AND DIP.BAC_WID = DI.BAC_WID GROUP BY DI.DISTRICT_CODE,DI.BILLING_ACCOUNT_NO,DI.INST_SEQUENCE_NO ) GROUP BY DISTRICT_CODE,BILLING_ACCOUNT_NO) T1, D_BILLING_ACCOUNT DB WHERE DB.DISTRICT_CODE = T1.DISTRICT_CODE(+) AND DB.BILLING_ACCOUNT_NO = T1.BILLING_ACCOUNT_NO(+) --089e010d8574a314a704daef9188 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

---------- Forwarded message ----------
From: suneel hadoop
Date: Monday, April 22, 2013
Subject:
To: user <user@hadoop.apache.org>, user-help@pig.apache.org, "user@hive.apache.org" <user@hive.apache.org>


Can any one help me to change this SQL to pig Latin

=A0

SELECT ('CSS'||DB.DISTRICT_CODE||DB.BILLING_ACCOUNT_NO) BAC_KEY,= =A0

CASE WHEN T1.TAC_142 IS NULL THEN 'N' ELS= E T1.TAC_142 END TAC_142 FROM=A0

(

=A0

SE= LECT DISTRICT_CODE,BILLING_ACCOUNT_NO,=A0

MAX(CASE WHEN TAC_1 =3D 'Y' AND (TAC_2 = =3D 'Y' OR TAC_3 =3D 'Y') THEN 'Y' ELSE 'N'= END) TAC_142 FROM=A0

(

<= span style=3D"line-height:normal;background-color:rgba(255,255,255,0)">SELE= CT DI.DISTRICT_CODE,DI.BILLING_ACCOUNT_NO,DI.INST_SEQUENCE_NO,=A0

MAX(CASE WHEN TRIM(DIP.PRODUCT_CODE) =3D 'A14= 493' AND UPPER(DI.HAZARD) LIKE '%999%EMERGENCY%LINE%' AND UPPER= (DI.WARNING) LIKE '%USE%999%ALERT%METHOD%' THEN 'Y' ELSE &#= 39;N' END) TAC_1,=A0

MAX(CASE WHEN TRIM(DIP.PRODUCT_TYPE) IN ('20&= #39;,'21') AND TRIM(DIP.MAINTENANCE_CONTRACT) IN ('E','= T') THEN 'Y' ELSE 'N' END) TAC_2,=A0

MAX(CASE WHEN TRIM(DIP.PRODUCT_CODE) IN ('A14= 498','A14428','A22640') THEN 'Y' ELSE 'N= 9; END) TAC_3=A0

FROM=A0

<= font>D_INSTALLATION DI,=A0

D_INSTALLATION_PRODUCT DIP=A0

WHERE=A0

DIP.INST_SEQUENCE_NO =3D DI.INST_SEQUENCE_NO AND= =A0

DIP.BAC_WID =3D DI.BAC_WID= =A0

GROUP BY DI.DISTRICT_CODE,DI.BILLING_ACCOUNT_NO,D= I.INST_SEQUENCE_NO

)=A0

GROUP BY DISTRICT_CODE,BILLING_ACCOUNT_NO)=

T1,

D_BILLING_ACCOUNT DB=A0

WHERE=A0=A0

DB.DISTRICT_CODE =3D T1.DISTRICT_CODE(+) AND=A0

DB.BILLING_ACCOUNT_NO =3D T1.BILL= ING_ACCOUNT_NO(+)


--089e010d8574a314a704daef9188--