Grokbase Groups Pig user April 2013
FAQ
Can anybody help on this to convert sql to pig for below query.

---------- Forwarded message ----------
From: suneel hadoop <suneel.bigdata@gmail.com>
Date: Mon, Apr 22, 2013 at 2:49 PM
Subject: SQL to pig latin
To: user <user@hadoop.apache.org>, "user@hive.apache.org" <
user@hive.apache.org>, user-help@pig.apache.org




---------- 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



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(+)

Search Discussions

  • Thejas Nair at Apr 25, 2013 at 12:24 am
    Raj,
    If you give a more general example of sql you want to convert, and
    also say what part of the sql query you are having difficulty
    converting, then it will be easier to help you.

    On Mon, Apr 22, 2013 at 11:44 AM, Raj hadoop wrote:
    Can anybody help on this to convert sql to pig for below query.

    ---------- Forwarded message ----------
    From: suneel hadoop <suneel.bigdata@gmail.com>
    Date: Mon, Apr 22, 2013 at 2:49 PM
    Subject: SQL to pig latin
    To: user <user@hadoop.apache.org>, "user@hive.apache.org" <
    user@hive.apache.org>, user-help@pig.apache.org




    ---------- 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



    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(+)

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categoriespig, hadoop
postedApr 22, '13 at 6:45p
activeApr 25, '13 at 12:24a
posts2
users2
websitepig.apache.org

2 users in discussion

Raj hadoop: 1 post Thejas Nair: 1 post

People

Translate

site design / logo © 2021 Grokbase