oracle 在SQL查询中显示其他列

lrl1mhuk 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(198)

我有一个SQL选择查询，它返回客户联系电子邮件地址信息，以发送下面给出的客户声明

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;

上述查询的结果是

PARTY_NAME  Bill_To_Number  Customer_Bill_To_Name   Customer_Email_Address
Party 1         1006746009  CUSTOM PRODUCTS         [email protected]
Party 1         1006746009  CUSTOM PRODUCTS         [email protected]
Party 2         1006746010  PAINT COMPANY           [email protected]
Party 2         1006746010  PAINT COMPANY           [email protected]
Party 3         1006746011  ADVANCED MATERIALS  [email protected]
Party 4         1006746012  ADVANCED PRODUCTS   [email protected]

我需要修改查询，以显示多一个额外的列，在选择语句（即，Dummy）中使用值“A”或“B”。导出该值的逻辑是，如果客户在第一条记录中有多封电子邮件，则该值应为“A”，而在第二条记录中，该值应为“B”。在上述输出中，Party1和Party2具有多个电子邮件ID，因此虚拟列应具有A作为第一条记录，B作为第二条记录，如下所示

PARTY_NAME  Bill_To_Number  Customer_Bill_To_Name   Customer_Email_Address   Dummy
Party 1         1006746009  CUSTOM PRODUCTS         [email protected]     A
Party 1         1006746009  CUSTOM PRODUCTS         [email protected]      B
Party 2         1006746010  PAINT COMPANY           [email protected]   A
Party 2         1006746010  PAINT COMPANY           [email protected]       B
Party 3         1006746011  ADVANCED MATERIALS  [email protected]       A
Party 4         1006746012  ADVANCED PRODUCTS   [email protected] A

注意：party_name列是主键列。
任何帮助我将不胜感激。

oracle

来源：https://stackoverflow.com/questions/77149753/display-additional-column-in-the-sql-query

1条答案

按热度按时间

kmpatx3s1#

为每个party_name/location_id/party_site_name组合的行编号，然后将该数字转换为字符：

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

fiddle

赞(0）回复(0）举报 2023-10-16

我来回答

oracle 在SQL查询中显示其他列

1条答案

相关问题

热门标签

最新问答