Hi friends,
I am new to PIG script. I need to convert below sql query to PIG script.
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(+)
--------------------------------------------------------------------------
I have tried to write a below inner query into PIG.
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
PIG Script
A = load 'D_INSTALLATION.txt';
B= load 'D_INSTALLATION_PRODUCT.txt';
C= join A by DI.INST_SEQUENCE_NO, B by INST_SEQUENCE_NO;
D= join C by BAC_WID,B by BAC_WID;
caseData = foreach D generate ((PRODUCT_CODE=='A14493' and
UPPER(HAZARD)=='999%EMERGENCY%LINE' and
UPPER(WARNING)=='USE%999%ALERT%METHOD') ? 'Y':'N') AS TAC_1,
((PRODUCT_TYPE IN ('20','21') and MAINTENANCE_CONTRACT IN
('E','T')) ? 'Y':'N') As TAC_2,
(PRODUCT_CODE IN('A14498','A22640','A22640') ? 'Y':'N') As TAC_3;
grouped = group caseData by
DISTRICT_CODE,BILLING_ACCOUNT_NO,INST_SEQUENCE_NO;
Data = foreach grouped generate group as
DISTRICT_CODE,BILLING_ACCOUNT_NO,INST_SEQUENCE_NO,MAX(TAC_1) as
TAC1,MAX(TAC_2) as TAC2,MAX(TAC_3) as TAC3
It is giving lot of errors. Can you please help me.. attached are the
tables
D_BILLING_ACCOUNT
Name Null Type
----------------------------- -------- -------------
BAC_WID NOT NULL NUMBER
DISTRICT_CODE VARCHAR2(6)
BILLING_ACCOUNT_NO NUMBER(8)
CSS_CUSTOMER_ID NUMBER(8)
CONSENT38_38A VARCHAR2(3)
CONSENT38 VARCHAR2(6)
CONSENT38A VARCHAR2(6)
CUSTOMER_CLASS VARCHAR2(3)
CSS_INTERNET_IND VARCHAR2(3)
BILL_GROUP_CODE VARCHAR2(30)
CUSTOMER_GROUPING VARCHAR2(9)
ORDERING_POLICY VARCHAR2(3)
SALES_AC_OWNER VARCHAR2(3)
BILLING_CONTACT VARCHAR2(6)
LATEST_CAMPAIGN VARCHAR2(3)
VIP VARCHAR2(3)
CSA_ID VARCHAR2(9)
CSS_FOLLOW_UP_RATING VARCHAR2(3)
DUTY_REFERENCE_ID VARCHAR2(36)
BUDGET_ACCOUNT_IND VARCHAR2(3)
BILL_TYPE VARCHAR2(3)
BILL_SERIAL_NO VARCHAR2(9)
BILL_MEDIA VARCHAR2(3)
FLEXIBLE_BILL_SUMMARY VARCHAR2(3)
CALL_ITEMISATION_OPTION VARCHAR2(3)
CALL_ITEMISATION_PRI_SORT_KEY VARCHAR2(3)
CALL_ITEMISATION_SEC_SORT_KEY VARCHAR2(3)
CALL_VALUE VARCHAR2(30)
CHRONICALLY_SICK_OR_DISABLED VARCHAR2(3)
BUDGET_AC_PAYMENT_DATE DATE
METHOD_OF_PAYMENT VARCHAR2(3)
PAYMENT_CHANNEL VARCHAR2(6)
BILL_DATE DATE
RECEIPT_METHOD VARCHAR2(6)
OCB_IND VARCHAR2(3)
DISC_IND VARCHAR2(3)
BUDGET_ACCOUNT_PAYMENT_DAY NUMBER(2)
UNDER_REASSESSMENT_IND NUMBER(1)
BUDGET_ACCOUNT_PAYMENT_AMOUNT NUMBER(12,2)
BUDGET_ACCOUNT_START_DATE DATE
CSS_DEBT_MANAGEMENT_IND VARCHAR2(3)
RENTAL_ITEMISATION_IND VARCHAR2(3)
SUPPRESS_FUTURE_BILLS VARCHAR2(3)
DIVERT_ALL_INVOICES VARCHAR2(3)
DIVERT_NEXT_INVOICE VARCHAR2(3)
VAT_INVOICE_IND VARCHAR2(3)
UNDER_ENQUIRY_IND VARCHAR2(3)
CAMPAIGN_EXCLUSIONS_IND VARCHAR2(3)
COPIES_ACCOUNT_IND NUMBER(1)
BILL_LAYOUT_ACCOUNT_IND VARCHAR2(24)
CURRENT_BILL_FREQUENCY VARCHAR2(3)
LOW_USAGE_SCHEME VARCHAR2(3)
MAI_LOW_USAGE_SCHEME VARCHAR2(3)
DD_PROVISION_DATE DATE
BANK_ACCOUNT_NAME VARCHAR2(54)
BANK_SORT_CODE NUMBER(6)
BANK_ACCOUNT_NO NUMBER(8)
CSS_UPPER_CALL_LEVEL NUMBER(9)
CONTACT_LIMIT NUMBER(9)
BILLING_ADDR_NAD_ALK VARCHAR2(36)
TS_LAST_UPDATED DATE
BILLING_TITLE VARCHAR2(84)
BILLING_FORENAME VARCHAR2(84)
BILLING_INITIALS VARCHAR2(84)
BILLING_SURNAME VARCHAR2(168)
BILLING_HONOURS VARCHAR2(168)
BILLING_LOCATION_DESCRIPTION VARCHAR2(84)
BILLING_ADDRESS_TYPE VARCHAR2(3)
BILLING_SUB_PREMISES VARCHAR2(84)
BILLING_PREMISES_NAME VARCHAR2(135)
BILLING_THOROUGHFARE_NUMB VARCHAR2(18)
BILLING_THOROUGHFARE_NAME VARCHAR2(171)
BILLING_SUB_LOCALITY VARCHAR2(105)
BILLING_POST_TOWN VARCHAR2(105)
BILLING_COUNTY VARCHAR2(105)
BILLING_POST_CODE VARCHAR2(24)
CUSTOMER_TS_LAST_UPD DATE
BAC_PROFILE_TS_LAST_UPD DATE
DATA_OWNER VARCHAR2(3)
RENTAL_END_DATE NUMBER(8)
PREFERRED_PAYT_DATE NUMBER(2)
ONE_BILL_IND NUMBER(1)
MONTROSE_IND VARCHAR2(3)
TEMP_UNBILLABLE_AC VARCHAR2(3)
VAT_EXMPT_CAT VARCHAR2(3)
INSTMENT_IND VARCHAR2(3)
DD_STATUS VARCHAR2(3)
FINAL_BILL_PENDING VARCHAR2(3)
FINAL_BILL_PRODUCED VARCHAR2(3)
NONCSS_BILL_IND VARCHAR2(3)
BILL_SUPPR_IND NUMBER(1)
RENTAL_START_DATE NUMBER(8)
E_BILL_INDICATOR VARCHAR2(3)
GEN_ACCOUNT_TYPE VARCHAR2(3)
NOVATION_IND VARCHAR2(3)
SUPP_ITEMIZATION_OPTCE VARCHAR2(3)
MM_SUSP_IND VARCHAR2(3)
SEL_ITEM_IND VARCHAR2(3)
BILL_SEQ_NO_001 NUMBER(3)
BILL_PD_ST_DT_001 DATE
BILL_PD_END_DT_001 DATE
PAYMNT_DUE_DT_001 DATE
INV_AMNT_001 NUMBER(12,3)
VAT_001 NUMBER(12,3)
BILL_SEQ_NO_002 NUMBER(3)
BILL_PD_ST_DT_002 DATE
BILL_PD_END_DT_002 DATE
PAYMNT_DUE_DT_002 DATE
INV_AMNT_002 NUMBER(12,3)
VAT_002 NUMBER(12,3)
MPP_REAS_DT DATE
GPC_END_IND NUMBER(1)
BILLED_CR_COUNT_001 NUMBER(5)
SPL_OFFER_CODE_001 VARCHAR2(21)
BILLED_CR_COUNT_002 NUMBER(5)
SPL_OFFER_CODE_002 VARCHAR2(21)
ONEBILL_ACCOUNT_NO VARCHAR2(36)
BILL_ACCNT_KEY VARCHAR2(90)
BILL_ACCNT_NAME VARCHAR2(300)
CSS_BILL_NAME VARCHAR2(300)
BILL_ACCNT_TYPE VARCHAR2(150)
COS_OM_CODE VARCHAR2(90)
SIM_FOLLOW_UP_RATING VARCHAR2(3)
PRICE_LIST VARCHAR2(150)
OCB_STATUS VARCHAR2(3)
TOS_STATUS VARCHAR2(3)
TOS_DATE DATE
PARENT_TYPE VARCHAR2(90)
ICB_STATUS VARCHAR2(3)
EXTERNAL_SYSTEM_INSTANCE VARCHAR2(30)
BILL_ACCNT_NUM VARCHAR2(90)
ASSET_SUMMARY NUMBER(9)
BILLING_OPTIONS_SUMMARY NUMBER(9)
SIM_DEBT_MANAGEMENT_IND VARCHAR2(3)
BILL_LAYOUT VARCHAR2(24)
SIM_UPPER_CALL_LEVEL NUMBER(22)
CONTACT_CALL_LEVEL NUMBER(22)
UNDER_ENQUIRY_FLG VARCHAR2(3)
CAMP_EXCLUSIONS VARCHAR2(90)
CSS_LUS_ACCNT_IND VARCHAR2(3)
LOCATION_DESCRIPTION VARCHAR2(300)
WLR2_FLG VARCHAR2(3)
TS_CREATED DATE
TS_LAST_UPD DATE
DELETED_FLG VARCHAR2(3)
DEBT_START_DATE DATE
DEBT_STATUS_FLG VARCHAR2(3)
VAT_EXEMPT_IND VARCHAR2(3)
BILL_ACCNT_SOURCE VARCHAR2(300)
BILLING_SCORE VARCHAR2(60)
BILLING_STATUS VARCHAR2(90)
WLR3_ASSET_FLAG VARCHAR2(3)
AVG_BILL_PAY_AMNT NUMBER(10)
RISK_FACTOR NUMBER(3,2)
ACCOUNT_START_DATE DATE
PARENT_BILL_ACCNT_KEY VARCHAR2(90)
SOURCE_SYSTEM CHAR(3)
ETL_CREATED_DT DATE
ETL_LAST_UPD_DT DATE
D_INSTALLATION
Name Null Type
------------------------- -------- -------------
INST_WID NUMBER
BAC_WID NUMBER
DISTRICT_CODE NOT NULL VARCHAR2(6)
BILLING_ACCOUNT_NO NOT NULL NUMBER(8)
INST_SEQUENCE_NO NOT NULL VARCHAR2(12)
SITE_NUMBER VARCHAR2(24)
MAIN_TEL_NO VARCHAR2(33)
TELEPHONE_FORMAT_CODE VARCHAR2(3)
MAIN_TEL_NO_STATUS VARCHAR2(3)
INST_DIRECTORY_CATEGORY VARCHAR2(3)
LINE_DIRECTORY_CATEGORY VARCHAR2(3)
INST_START_DATE DATE
MAIN_LINE_START_DATE DATE
INST_STATUS VARCHAR2(3)
INST_CLASSIFICATION VARCHAR2(6)
CUSTOMER_TYPE VARCHAR2(6)
RENTAL_TARIFF_GROUP VARCHAR2(6)
STERLING_TARIFF_GROUP VARCHAR2(6)
CURRENT_STATUS VARCHAR2(18)
TOS_DATE DATE
HAZARD VARCHAR2(60)
WARNING VARCHAR2(60)
PRIORITY VARCHAR2(3)
EXCHANGE_CODE VARCHAR2(18)
INST_EXCHANGE_GROUP_CODE VARCHAR2(15)
SERVING_DP VARCHAR2(18)
A1141_CODE VARCHAR2(27)
LINE_STATUS VARCHAR2(3)
LINE_CLASS VARCHAR2(9)
LINE_CLASS_DESCRIPTION VARCHAR2(15)
LINE_ACCESS VARCHAR2(9)
INST_ADDR_NAD_ALK VARCHAR2(36)
TS_LAST_UPDATED DATE
INST_TITLE VARCHAR2(84)
INST_FORENAME VARCHAR2(84)
INST_INITIALS VARCHAR2(84)
INST_SURNAME VARCHAR2(168)
INST_HONOURS VARCHAR2(168)
INST_LOCATION_DESCRIPTION VARCHAR2(84)
INST_ADDRESS_TYPE VARCHAR2(3)
INST_SUB_PREMISES VARCHAR2(84)
INST_PREMISES_NAME VARCHAR2(135)
INST_THOROUGHFARE_NUMB VARCHAR2(18)
INST_THOROUGHFARE_NAME VARCHAR2(171)
INST_SUB_LOCALITY VARCHAR2(105)
INST_POST_TOWN VARCHAR2(105)
INST_COUNTY VARCHAR2(105)
INST_POST_CODE VARCHAR2(24)
CESSATION_REASON VARCHAR2(3)
NUMBER_PORTED_FLAG VARCHAR2(3)
TBG_ID VARCHAR2(24)
DATA_OWNER VARCHAR2(3)
RETAILER_ID VARCHAR2(9)
WLR3_FLAG VARCHAR2(3)
SOURCE_SYSTEM CHAR(3)
ETL_CREATED_DT DATE
ETL_LAST_UPD_DT DATE
INST_ADDRESS_KEY VARCHAR2(90)
D_INSTALLATION_PRODUCT
Name Null Type
-------------------------- -------- ------------
INST_PROD_WID NOT NULL NUMBER
BAC_WID NUMBER
DISTRICT_CODE VARCHAR2(6)
BILLING_ACCOUNT_NO NUMBER(8)
INST_SEQUENCE_NO VARCHAR2(12)
PRODUCT_CODE VARCHAR2(18)
CONTRACT_TYPE VARCHAR2(6)
MAINTENANCE_CONTRACT VARCHAR2(3)
EXCHANGE_LINE_INDICATOR VARCHAR2(3)
PRODUCT_TYPE VARCHAR2(9)
QUANTITY NUMBER(7)
FIRST_CPH_DATE DATE
LAST_CPH_DATE DATE
FIRST_CPH_TERM_EXPIRY_DATE DATE
LAST_CPH_TERM_EXPIRY_DATE DATE
LAST_CPH_ORDER_NO VARCHAR2(18)
TS_LAST_UPDATED DATE
DATA_OWNER VARCHAR2(3)
SOURCE_SYSTEM CHAR(3)
ETL_CREATED_DT DATE
ETL_LAST_UPD_DT DATE
