我正在处理一个复杂的更新，我需要根据另一个表的输出更新一个表中的数据。
场景：
表1：HZ_CUST_ACCOUNTS（FK PARTY_ID FROM HZ_PARTIES）
| 客户账户ID| CUST_ACCOUNT_ID |
| --| ------------ |
| 五六七| 567 |
表2 HZ_PARTIES
| 姓名| NAME |
| --| ------------ |
| XYZ| XYZ |
表3 HZ_CONTACT_POINTS（FK PARTY_ID FROM HZ_PARTIES）
| 电子邮件|OWNER_TABLE_NAME| OWNER_TABLE_NAME |
| --|--| ------------ |
| XYZ@GMAIL|联系我们| HZ_PARTIES |
| 电子邮件|HZ_PARTY_网站| HZ_PARTY_SITES |
表4：Customer_dimension_table
| 客户ID| customer_id |
| --| ------------ |
| 八七九| 879 |
表5：Contact_dimension_table（目标表）
| 名称，名称|电子邮件| email |
| --|--| ------------ |
| XYZ|空值| NULL |
| XYZ|空值| NULL |
| ABC|空值| NULL |
我需要你的专业知识，填补了上述源表的基础上为XYZ的电子邮件地址。这种转变可能吗？我试图在oracle ebs中获取联系人，但在主查询中，它为一些当事人提供了错误的电子邮件地址，因为一些当事人的party_id（hz_parties）与party_site_id（in HZ_party_sites）匹配。因此，我想做一个更新，以便它可以更新正确的电子邮件地址，但更新方法似乎不起作用。
下面是有问题的查询：
SELECT hcar.cust_account_role_id,
hcar.current_role_state,
hcar.role_type,
hcar.status,
hcar.cust_account_id,
hcar.cust_acct_site_id/* , ( SELECT hp.party_name
FROM hz_parties hp
WHERE 1=1
AND hp.party_id = hcar.party_id
) contact_name */
,
(SELECT hp.party_name
FROM hz_parties hp, hz_relationships hr
WHERE 1 = 1
AND hr.party_id = hcar.party_id
AND hp.party_id = hr.object_id
AND hr.object_type = 'PERSON'
AND hr.relationship_code IN
('CONTACT', 'EMPLOYER_OF'))
contact_name,
hcp.contact_point_id,
hcp.phone_country_code,
NVL(hcp.phone_area_code, (SELECT hcp.phone_area_code
FROM hz_contact_points hcp
WHERE 1 = 1
AND hcp.owner_table_id = hcar.party_id
AND hcp.phone_line_type(+) = 'GEN'
AND hcp.phone_area_code IS NOT NULL
--AND hcp.primary_flag(+) = 'Y'
AND hcp.contact_point_id =
(SELECT MAX (hcp2.contact_point_id)
FROM hz_contact_points hcp2
WHERE 1 = 1
AND hcp2.phone_line_type(+) = 'GEN'
AND hcp2.primary_flag(+) = 'Y'
AND hcp2.owner_table_id =
hcp.owner_table_id))) phone_area_code,
NVL(hcp.phone_number, (SELECT hcp.phone_number
FROM hz_contact_points hcp
WHERE 1 = 1
AND hcp.owner_table_id = hcar.party_id
AND hcp.phone_line_type(+) = 'GEN'
--AND hcp.primary_flag(+) = 'Y'
AND hcp.phone_number IS NOT NULL
AND hcp.contact_point_id =
(SELECT MAX (hcp2.contact_point_id)
FROM hz_contact_points hcp2
WHERE 1 = 1
AND hcp2.phone_line_type(+) = 'GEN'
--AND hcp2.primary_flag(+) = 'Y'
AND hcp2.owner_table_id =
hcp.owner_table_id))) phone_number,
NVL(hcp.phone_extension, (SELECT hcp.phone_extension
FROM hz_contact_points hcp
WHERE 1 = 1
AND hcp.owner_table_id = hcar.party_id
AND hcp.phone_line_type(+) = 'GEN'
-- AND hcp.primary_flag(+) = 'Y'
AND hcp.phone_extension IS NOT NULL
AND hcp.contact_point_id =
(SELECT MAX (hcp2.contact_point_id)
FROM hz_contact_points hcp2
WHERE 1 = 1
AND hcp2.phone_line_type(+) = 'GEN'
--AND hcp2.primary_flag(+) = 'Y'
AND hcp2.owner_table_id =
hcp.owner_table_id))) phone_extension ,
NVL(hcp.email_address,
NVL(( SELECT MAX(SUBSTR (hcp.email_address, 1, 50))
FROM hz_contact_points hcp
WHERE 1 = 1
AND hcp.owner_table_id IN (SELECT hr3.party_id
FROM hz_parties hp3,
hz_relationships hr3,
hz_cust_account_roles hcar3
WHERE 1 = 1
AND hr3.party_id = hcar3.party_id
AND hp3.party_id = hr3.object_id
AND hr3.object_type = 'PERSON'
AND hr3.relationship_code IN
('CONTACT', 'EMPLOYER_OF')
and hcar.cust_account_id=hcar3.cust_account_id
and hp3.party_name = (SELECT hp1.party_name
FROM hz_parties hp1, hz_relationships hr1
WHERE 1 = 1
AND hr1.party_id= hcar.party_id
AND hp1.party_id = hr1.object_id
AND hr1.object_type = 'PERSON'
AND hr1.relationship_code IN
('CONTACT', 'EMPLOYER_OF')) )
--= hcar.party_id
AND hcp.contact_point_type = 'EMAIL'
AND hcp.email_address IS NOT NULL
),
( (SELECT hp.email_address
FROM hz_parties hp
WHERE 1 = 1
AND hp.party_id = hcar.party_id ))
)
) email_address,
(SELECT DECODE (hcp2.contact_point_type,
'TLX', hcp2.telex_number,
hcp2.phone_number)
FROM hz_contact_points hcp2, fnd_lookup_values flv
WHERE 1 = 1
AND hcp2.contact_point_id = hcp.contact_point_id
AND hcp2.contact_point_type NOT IN ('EDI')
AND hcp2.owner_table_id = hcar.party_id
AND hcp2.primary_flag = 'Y'
AND NVL (hcp2.phone_line_type,
hcp2.contact_point_type) = 'FAX'
AND NVL (hcp2.phone_line_type,
hcp2.contact_point_type) = flv.lookup_code
AND ( ( flv.lookup_type = 'COMMUNICATION_TYPE'
AND flv.lookup_code IN
('PHONE', 'TLX', 'EMAIL', 'WEB'))
OR (flv.lookup_type = 'PHONE_LINE_TYPE'))
AND flv.language = USERENV ('LANG')
AND flv.view_application_id = 222
AND flv.security_group_id = 0)
fax,
(SELECT SUBSTR (NVL (hoc.job_title, hoc.job_title_code),
1,
250)
FROM hz_org_contacts hoc, hz_relationships hr
WHERE 1 = 1
AND hoc.party_relationship_id = hr.relationship_id
AND hr.party_id = hcar.party_id
AND hr.object_type = 'ORGANIZATION'
AND hr.directional_flag = 'F')
job_title,
'ORACLE' data_source,
hcp.primary_flag
FROM hz_cust_account_roles hcar, hz_contact_points hcp
WHERE 1 = 1
-- AND hcar.current_role_state = 'A'
AND hcp.owner_table_id(+) = hcar.party_id
AND hcp.primary_flag(+) = 'Y'
AND hcar.party_id IN
(SELECT hr.party_id
FROM hz_org_contacts hoc, hz_relationships hr
WHERE 1 = 1
AND hoc.party_relationship_id =
hr.relationship_id
AND hr.object_type = 'ORGANIZATION'
AND hr.directional_flag = 'F'
AND hoc.last_update_date > SYSDATE -5
UNION
SELECT party_id
FROM hz_cust_account_roles
WHERE last_update_date > SYSDATE - 5
)字符串
1条答案
按热度按时间wn9m85ua1#
类似于：
字符串
如果需要匹配名称和
customer_id，则可能还需要包括HZ_PARTIES。型