On January 27th, this site will be read-only as we migrate to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,529 Users
  • 2,269,755 Discussions
  • 7,916,772 Comments

Discussions

NULL - Table Records Fetch - HZ_CONTACT_POINTS

User_FHYJ0
User_FHYJ0 Member Posts: 31 Red Ribbon
edited May 4, 2020 10:00AM in SQL & PL/SQL

Hello All,

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE 11.2.0.4.0 ProductionTNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 - Production

Need to fetch all the Customers for which no E-Mail address is generated in HZ_CONTACT_POINTS table.

If E-Mail address is available then, a new entry (a line) will be present in HZ_CONTACT_POINTS table with CONTACT_POINT_TYPE as "E-Mail".

If No E-mail address, then in HZ_CONTACT_POINTS table there will be no data or NULL.

I have framed this query, keeping an outer join of "AND hps.party_site_id = hcp.owner_table_id(+)"

But it is not giving the desired results. It is fetching even the records which has CONTACT_POINT_TYPE as "Phone" and "E-Mail" in HZ_CONTACT_POINTS table.

If i include the condition, AND (hcp.email_address IS NULL AND hcp.contact_point_type NOT IN ('EMAIL','PHONE')) then none of the Customers are fetching in the output.

Let me know your thoughts on the same.

  SELECT DISTINCT hca.account_number customer_num,                  acv.customer_name customer_name,                  hca.attribute5 mid,                  hca.creation_date,                  hca.status,                  hcsu.site_use_code,                  hl.address1,                  hl.address2,                  hl.address3,                  hl.address4,                  hl.city,                  hl.postal_code,                  hl.country,                  hcp.contact_point_type,                  hcp.email_address contact_email,                  hcp.phone_area_code || ' ' || hcp.phone_number contact_phone,                  hcp.url contact_web    FROM apps.ar_customers_v acv,         apps.ar_addresses_v aav,         apps.ar_contacts_v acc,         apps.ar_contact_roles_v acr,         apps.hz_phone_country_codes hpc,         apps.hz_cust_site_uses_all hcsu,         apps.hz_cust_acct_sites_all hcasa,         apps.hz_cust_accounts hca,         apps.hz_party_sites hps,         apps.hz_contact_points hcp,         apps.hz_locations hl   WHERE     1 = 1         AND hcsu.site_use_code = 'BILL_TO'         AND hcsu.status = 'A'         AND hcasa.cust_acct_site_id = hcsu.cust_acct_site_id         AND hcasa.party_site_id = hps.party_site_id         AND hps.location_id = hl.location_id         AND hps.status = 'A'         AND hps.party_site_id = hcp.owner_table_id(+)         AND hca.cust_account_id = acv.customer_id         AND acv.customer_id = aav.customer_id         AND acv.status = 'A'         AND aav.address_id = acc.address_id(+)         AND aav.status = 'A'         AND acr.contact_id(+) = acc.contact_id         AND hpc.territory_code = aav.country         AND aav.address_id = hcsu.cust_acct_site_id         AND hcasa.status = 'A'         AND hca.attribute20 = NVL (1234, hca.attribute20)             AND hcsu.primary_flag = 'Y'--         AND (hcp.email_address IS NULL AND hcp.contact_point_type NOT IN ('EMAIL','PHONE')) ORDER BY customer_num;

Regards,

SG

Tagged:
BEDEBluShadowFrank KulashUser_FHYJ0

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,867 Blue Diamond
    edited May 4, 2020 7:01AM Answer ✓

    You need to include the 'EMAIL' as part of the left join.  Maybe something like this...

    If you posted table creation and insert scripts it would be easy to test.

    1. SELECT acv.customer_number, hcp.* 
    2.     FROM apps.ar_customers_v acv 
    3.         JOIN apps.hz_cust_acct_sites_all hcasa 
    4.             ON hcasa.cust_account_id = acv.customer_id 
    5.         JOIN apps.hz_party_sites hps 
    6.             ON hcasa.party_site_id = hps.party_site_id 
    7.         JOIN apps.hz_cust_site_uses_all hcsu 
    8.             ON hcasa.cust_acct_site_id = hcsu.cust_acct_site_id 
    9.         LEFT OUTER JOIN apps.hz_contact_points hcp 
    10.             ON    hps.party_site_id = hcp.owner_table_id and hcp.contact_point_type = 'EMAIL'
    11.   WHERE    1 = 1 
    12.         AND hcsu.site_use_code = 'BILL_TO' 
    13.         AND hcsu.primary_flag = 'Y' 
    14.         AND hcsu.status = 'A' 
    15.         AND acv.status = 'A' 
    16.         AND hcasa.status = 'A' 
    17.         AND hps.status = 'A' 
    18.         AND acv.attribute20 = NVL (1234, acv.attribute20) 
    19.         AND hcp.contact_point_type is null
    20.         AND acv.customer_number IN (664402, 
    21.                                     664403, 
    22.                                     664404, 
    23.                                     664405, 
    24.                                     664406, 
    25.                                     664407) 
    26. ORDER BY acv.customer_number; 
    User_FHYJ0
«1

Answers

  • BEDE
    BEDE Oracle Developer Bucharest, RomaniaMember Posts: 2,484 Gold Trophy
    edited May 4, 2020 3:19AM

    AND (hcp.email_address IS NULL AND hcp.contact_point_type NOT IN ('EMAIL','PHONE'))  is wrong as condition. If there is no corresponding record in apps.hz_contact_points, then hcp.contact_point_type will be null, meaning that it will not be IN ('EMAIL','PHONE')). I suppose an or should be used instead of and.

  • User_FHYJ0
    User_FHYJ0 Member Posts: 31 Red Ribbon
    edited May 4, 2020 3:24AM

    Hi Bede,

    Thanks for your reply.

    If i include OR condition, then none of the records are returned even though there are lot of Customers for which E-Mail address is NULL in HZ_CONTACT_POINTS table.

    AND (hcp.email_address IS NULL OR hcp.contact_point_type NOT IN ('EMAIL','PHONE'))

  • BEDE
    BEDE Oracle Developer Bucharest, RomaniaMember Posts: 2,484 Gold Trophy
    edited May 4, 2020 3:43AM

    So, it it that you actually want all the customers that do not have e-mail? And, if so, then why join with  apps.hz_contact_points for e-mail? You should use a not exists or not in to get those for which there is no e-mail addtess in apps.hz_contact_points. I'm not sure if the join conditions are right, but I think it should be something like below:

    SELECT DISTINCT hca.account_number customer_num, 
                      acv.customer_name customer_name, 
                      hca.attribute5 mid, 
                      hca.creation_date, 
                      hca.status, 
                      hcsu.site_use_code, 
                      hl.address1, 
                      hl.address2, 
                      hl.address3, 
                      hl.address4, 
                      hl.city, 
                      hl.postal_code, 
                      hl.country, 
                      hcp.contact_point_type, 
                      hcp.email_address contact_email, 
                      hcp.phone_area_code || ' ' || hcp.phone_number contact_phone, 
                      hcp.url contact_web 
        FROM apps.ar_customers_v acv, 
             apps.ar_addresses_v aav, 
             apps.ar_contacts_v acc, 
             apps.ar_contact_roles_v acr, 
             apps.hz_phone_country_codes hpc, 
             apps.hz_cust_site_uses_all hcsu, 
             apps.hz_cust_acct_sites_all hcasa, 
             apps.hz_cust_accounts hca, 
             apps.hz_party_sites hps, 
             apps.hz_contact_points hcp, 
             apps.hz_locations hl 
       WHERE     1 = 1 
             AND hcsu.site_use_code = 'BILL_TO' 
             AND hcsu.status = 'A' 
             AND hcasa.cust_acct_site_id = hcsu.cust_acct_site_id 
             AND hcasa.party_site_id = hps.party_site_id 
             AND hps.location_id = hl.location_id 
             AND hps.status = 'A' 
             AND hps.party_site_id = hcp.owner_table_id(+) 
             AND hca.cust_account_id = acv.customer_id 
             AND acv.customer_id = aav.customer_id 
             AND acv.status = 'A' 
             AND aav.address_id = acc.address_id(+) 
             AND aav.status = 'A' 
             AND acr.contact_id(+) = acc.contact_id 
             AND hpc.territory_code = aav.country 
             AND aav.address_id = hcsu.cust_acct_site_id 
             AND hcasa.status = 'A' 
             AND hca.attribute20 = NVL (1234, hca.attribute20)     
             AND hcsu.primary_flag = 'Y' 
             AND not exists (select 1

                   from apps.hz_contact_points hcp2

                   where  hps.party_site_id = hcp2.owner_table_id

                       and hcp2.email_address IS NULL

                   )
    ORDER BY customer_num; 

  • User_FHYJ0
    User_FHYJ0 Member Posts: 31 Red Ribbon
    edited May 4, 2020 3:55AM

    Yes, I want all the Customers for which E-Mail address is not available in HZ_CONTACT_POINTS table. (which means during Customer Creation, E-Mail was not provided.)

    I have created 3 customers which does not have E-Mail address in the table.

    Actually for the Customer there is a Party Site ID in hz_party_sites, whereas there is No record available in HZ_CONTACT_POINTS since no e-mail is provided. Hence it is NULL which is why i used Outer join.

    AND hps.party_site_id = hcp.owner_table_id ( + )

    But the below query did not fetch those 3 new records in the query.

      SELECT DISTINCT hca.account_number customer_num,                  acv.customer_name customer_name,                  hca.attribute5 mid,                  hca.creation_date,                  hca.status,                  hcsu.site_use_code,                  hl.address1,                  hl.address2,                  hl.address3,                  hl.address4,                  hl.city,                  hl.postal_code,                  hl.country--                  ,--                  hcp.contact_point_type,--                  hcp.email_address contact_email,--                  hcp.phone_area_code || ' ' || hcp.phone_number contact_phone,--                  hcp.url contact_web    FROM apps.ar_customers_v acv,        apps.ar_addresses_v aav,        apps.ar_contacts_v acc,        apps.ar_contact_roles_v acr,        apps.hz_phone_country_codes hpc,        apps.hz_cust_site_uses_all hcsu,        apps.hz_cust_acct_sites_all hcasa,        apps.hz_cust_accounts hca,        apps.hz_party_sites hps,--        apps.hz_contact_points hcp,        apps.hz_locations hl  WHERE    1 = 1        AND hcsu.site_use_code = 'BILL_TO'        AND hcsu.status = 'A'        AND hcasa.cust_acct_site_id = hcsu.cust_acct_site_id        AND hcasa.party_site_id = hps.party_site_id        AND hps.location_id = hl.location_id        AND hps.status = 'A'--        AND hps.party_site_id = hcp.owner_table_id(+)        AND hca.cust_account_id = acv.customer_id        AND acv.customer_id = aav.customer_id        AND acv.status = 'A'        AND aav.address_id = acc.address_id(+)        AND aav.status = 'A'        AND acr.contact_id(+) = acc.contact_id        AND hpc.territory_code = aav.country        AND aav.address_id = hcsu.cust_acct_site_id        AND hcasa.status = 'A'        AND hca.attribute20 = NVL (1234, hca.attribute20)        AND hcsu.primary_flag = 'Y'        AND NOT EXISTS                (SELECT 1                  FROM apps.hz_contact_points hcp2                  WHERE    hps.party_site_id = hcp2.owner_table_id                        AND hcp2.email_address IS NULL                        )ORDER BY customer_num;
  • BEDE
    BEDE Oracle Developer Bucharest, RomaniaMember Posts: 2,484 Gold Trophy
    edited May 4, 2020 4:07AM

    So, some joins are wrong...  Start rewriting the query adding the joins one by one. So you will see where you have something that should not be joined or should be outer joined. Why don't you use the ANSI syntax instead of the old Oracle syntax? ANSI syntax leads to queries where the join conditions are easier to follow.

    BluShadow
  • User_FHYJ0
    User_FHYJ0 Member Posts: 31 Red Ribbon
    edited May 4, 2020 4:54AM

    Hi Bede,

    Yes, i have split the query as below.

    I have created 3 Customers, Customer Numbers are 664402,664403 and 664404

    SELECT * FROM apps.ar_customers_v acv

    WHERE  customer_number in(664402,664403,664404);

    1.JPG

    SELECT hps.party_site_id

      FROM apps.hz_cust_acct_sites_all hcasa, apps.hz_party_sites hps,apps.hz_cust_site_uses_all hcsu

    WHERE    hcasa.party_site_id = hps.party_site_id

            AND hcsu.site_use_code = 'BILL_TO'

            AND hcsu.status = 'A'

            AND hcasa.cust_acct_site_id = hcsu.cust_acct_site_id

            AND hcasa.cust_account_id IN (59385711,59385712,59385713);

    2.JPG

    SELECT *

    FROM apps.hz_contact_points hcp

    WHERE owner_table_id in (SELECT hps.party_site_id

      FROM apps.hz_cust_acct_sites_all hcasa, apps.hz_party_sites hps,apps.hz_cust_site_uses_all hcsu

    WHERE    hcasa.party_site_id = hps.party_site_id

            AND hcsu.site_use_code = 'BILL_TO'

            AND hcsu.status = 'A'

            AND hcasa.cust_acct_site_id = hcsu.cust_acct_site_id

            AND hcasa.cust_account_id IN (59385711,59385712,59385713)

          );

    3.JPG

    In this case, there are 3 records for contact point type - PHONE since i have provided the Telephone numbers not for E-MAIL.

    I would require such Customers for which there is no entry for EMAIL in CONTACT_POINT_TYPE of HZ_CONTACT_POINTS table.

    Thanks

  • Paulzip
    Paulzip Member Posts: 8,867 Blue Diamond
    edited May 4, 2020 5:34AM

    If I've understood you correctly, you really need to start using modern ANSI joins, rather than SQL 89 Oracle joins.

    Then your problem is a simple left join on

    1. Left join on hcp2.owner_table_id AND hcp2.email_address = 'E-Mail'

    And in the where clause, the predicate to make an anti join check

    hcp2.email_address is null

    If you can have multiple, use a where not exists

    BEDEFrank KulashUser_FHYJ0
  • User_FHYJ0
    User_FHYJ0 Member Posts: 31 Red Ribbon
    edited May 4, 2020 6:11AM

    Hi Bede,

    Below are the Customers created.

    664402 - Got Phone number, No Email.

    664403 - Got Phone number, No Email.

    664404 - Got Phone number, No Email.

    664405 - No Phone number,  No Email.

    664406 - No Phone number,  No Email.

    664407 - Got Phone number, Got Email.

    Expected Output:

    664402

    664403

    664404

    664405

    664406

    There will be 2 records for  (664407)  in HZ_CONTACT_POINTS; 1 with Contact_Point_Type as PHONE and Other record with 'EMAIL' type.

    I have framed joining the above tables. But 664407 should not be fetched, but is fetched as well since there is an entry for PHONE ignoring EMAIL record. Even this should not fetched, since it belongs to same Customer.

    SELECT acv.customer_number,hcp.*FROM    apps.ar_customers_v acv,        apps.hz_cust_acct_sites_all hcasa,        apps.hz_party_sites hps,        apps.hz_cust_site_uses_all hcsu,        apps.hz_contact_points hcpWHERE  hcasa.cust_account_id = acv.customer_id        AND hcasa.party_site_id = hps.party_site_id        AND hcsu.site_use_code = 'BILL_TO'        AND hcsu.primary_flag = 'Y'        AND hcsu.status = 'A'        AND acv.status = 'A'        AND hcasa.status = 'A'        AND hps.status = 'A'        AND hcasa.cust_acct_site_id = hcsu.cust_acct_site_id        AND hps.party_site_id = hcp.owner_table_id(+)        AND acv.attribute20 = NVL (1234, acv.attribute20)        AND NVL(hcp.contact_point_type,'XXX') <>NVL('EMAIL','XXX')--        AND NVL(hcp.contact_point_type,'XXX') <>NVL('PHONE','XXX')        AND acv.customer_number  in(664402,664403,664404,664405,664406,664407)        order by acv.customer_number;

    4.JPG

  • User_FHYJ0
    User_FHYJ0 Member Posts: 31 Red Ribbon
    edited May 4, 2020 6:14AM

    Thanks Paul for your reply.

    Let me modify the query to ANSI and check for expected results.

  • User_FHYJ0
    User_FHYJ0 Member Posts: 31 Red Ribbon
    edited May 4, 2020 6:25AM

    Hi Paul,

    I have modified the query as below:

      SELECT acv.customer_number, hcp.*    FROM apps.ar_customers_v acv        JOIN apps.hz_cust_acct_sites_all hcasa            ON hcasa.cust_account_id = acv.customer_id        JOIN apps.hz_party_sites hps            ON hcasa.party_site_id = hps.party_site_id        JOIN apps.hz_cust_site_uses_all hcsu            ON hcasa.cust_acct_site_id = hcsu.cust_acct_site_id        LEFT OUTER JOIN apps.hz_contact_points hcp            ON    hps.party_site_id = hcp.owner_table_id  WHERE    1 = 1        AND hcsu.site_use_code = 'BILL_TO'        AND hcsu.primary_flag = 'Y'        AND hcsu.status = 'A'        AND acv.status = 'A'        AND hcasa.status = 'A'        AND hps.status = 'A'        AND acv.attribute20 = NVL (1234, acv.attribute20)        AND NVL (hcp.contact_point_type, 'XXX') <> NVL ('EMAIL', 'XXX')        AND acv.customer_number IN (664402,                                    664403,                                    664404,                                    664405,                                    664406,                                    664407)ORDER BY acv.customer_number;

    I am still receiving the same results. Please correct me. Thanks!

    6.JPG