I have a SQL select query which returns the Customer Contact Email Address information to send the Customer Statements given below
SELECT hp.party_name
,hzl.location_id "Bill To Number"
,hzps.party_site_name "Customer Bill To Name"
,hcp.email_address "Customer Email Address"
FROM
hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all hcsu,
hz_party_sites hzps ,
hz_locations hzl,
hz_cust_account_roles hcar,
hz_contact_points hcp,
hz_relationships hr
WHERE 1=1
and hp.party_id = hca.party_id
and hca.cust_account_id = hcsa.cust_account_id
and hcsa.cust_acct_site_id = hcsu.cust_acct_site_id
and hcsa.party_site_id = hzps.party_site_id(+)
and hzps.location_id = hzl.location_id(+)
and hcsu.site_use_code = 'BILL_TO'
and NVL(hcar.status,'A') ='A'
and hcar.cust_account_id = hca.cust_account_id
and hcar.cust_acct_site_id = hcsa.cust_acct_site_id
and hcar.relationship_id = hcp.relationship_id
and hcp.contact_point_type = 'EMAIL'
and nvl(hcp.end_date,SYSDATE+5) > SYSDATE
and hcp.relationship_id = hr.relationship_id
and hr.relationship_code = 'CONTACT_OF'
and hr.object_id = hp.party_id;Result of the above query is
PARTY_NAME Bill_To_Number Customer_Bill_To_Name Customer_Email_Address
Party 1 1006746009 CUSTOM PRODUCTS newton@testemail.net
Party 1 1006746009 CUSTOM PRODUCTS lorne@testemail.net
Party 2 1006746010 PAINT COMPANY tgliwell@testemail.com
Party 2 1006746010 PAINT COMPANY APAL@testemail.com
Party 3 1006746011 ADVANCED MATERIALS invo@testemail.com
Party 4 1006746012 ADVANCED PRODUCTS accpayable@testemail.comI need to modify the query to display one more additional column using in the select statement(i.e., Dummy) with values either 'A' or 'B'. Logic to derive the value is, if a Customer having multiple emails for the first record it should be 'A' and for the second record it should be 'B'. In the above output Party1 and Party2 are having multiple email ids so Dummy column should have A for first record and B for second record as given below
PARTY_NAME Bill_To_Number Customer_Bill_To_Name Customer_Email_Address Dummy
Party 1 1006746009 CUSTOM PRODUCTS newton@testemail.net A
Party 1 1006746009 CUSTOM PRODUCTS lorne@testemail.net B
Party 2 1006746010 PAINT COMPANY tgliwell@testemail.com A
Party 2 1006746010 PAINT COMPANY APAL@testemail.com B
Party 3 1006746011 ADVANCED MATERIALS invo@testemail.com A
Party 4 1006746012 ADVANCED PRODUCTS accpayable@testemail.com ANote: party_name column is the Primary key column.
Any help would me much appreciated.
Number the rows for each party_name/location_id/party_site_name combination and then convert that number to a character:
SELECT hp.party_name
,hzl.location_id "Bill To Number"
,hzps.party_site_name "Customer Bill To Name"
,hcp.email_address "Customer Email Address"
,CHR(
64
+ ROW_NUMBER() OVER (
PARTITION BY hp.party_name, hzl.location_id, hzps.party_site_name
ORDER BY hcp.email_address
)
) AS Dummy
FROM hz_parties hp
INNER JOIN hz_cust_accounts hca
ON hp.party_id = hca.party_id
INNER JOIN hz_cust_acct_sites_all hcsa
ON hca.cust_account_id = hcsa.cust_account_id
INNER JOIN hz_cust_site_uses_all hcsu
ON hcsa.cust_acct_site_id = hcsu.cust_acct_site_id
LEFT OUTER JOIN hz_party_sites hzps
ON hcsa.party_site_id = hzps.party_site_id
LEFT OUTER JOIN hz_locations hzl
ON hzps.location_id = hzl.location_id
INNER JOIN hz_relationships hr
ON hr.object_id = hp.party_id
INNER JOIN hz_contact_points hcp
ON hcp.relationship_id = hr.relationship_id
INNER JOIN hz_cust_account_roles hcar
ON hcar.cust_account_id = hca.cust_account_id
and hcar.cust_acct_site_id = hcsa.cust_acct_site_id
and hcar.relationship_id = hcp.relationship_id
WHERE hcsu.site_use_code = 'BILL_TO'
and (hcar.status = 'A' OR hcar.status IS NULL)
and hcp.contact_point_type = 'EMAIL'
and (hcp.end_date > SYSDATE OR hcp.end_date IS NULL)
and hr.relationship_code = 'CONTACT_OF';