Bdtjtk

I got a problem, i wanted to obtain the maximum length of a field because i wanted to know if there was fields that exceeded the size established for that field, for example : varchar2(20)

I am using oracle 11g, take a look at this:

https://i.stack.imgur.com/4DdWM.jpg

So, i did this query:

    SELECT

    hp.party_name                              

  , hca.account_number

  , hca.cust_account_id                        

 -- , hcsu.LOCATION customer_site_name

  , hcas.cust_acct_site_id                     

  , hcp.phone_number

  , hcp.email_address

  , hl.address1

  , hl.address2

  , hl.address3

  , hl.address4

  , hl.city

  , hl.province

  , hl.postal_code

  , hcas.status                                

  , DECODE( hcas.attribute5, 'PUP', 'Y', 'N' ) 

  , hca.status                                 

FROM apps.hz_cust_accounts hca

INNER JOIN apps.hz_cust_acct_sites_all hcas ON hca.cust_account_id = hcas.cust_account_id

INNER JOIN apps.hz_party_sites hps ON hcas.party_site_id = hps.party_site_id

INNER JOIN apps.hz_locations hl ON hps.location_id = hl.location_id

INNER JOIN apps.hz_parties hp ON hps.party_id = hp.party_id

LEFT JOIN (

        SELECT

            owner_table_id

          , max(case when contact_point_type = 'PHONE' then phone_number end) phone_number

          , max(case when contact_point_type = 'EMAIL' then email_address end) email_address

        FROM hz_contact_points

        WHERE status = 'A'

        AND primary_flag = 'Y'

        AND owner_table_name = 'HZ_PARTY_SITES'

        AND contact_point_type IN ('EMAIL','PHONE')

        AND length (phone_number) > 40

        GROUP BY 

            owner_table_id

    ) hcp ON hcas.party_site_id = hcp.owner_table_id 

WHERE hcas.status = 'A'

AND hps.status = 'A'

AND hca.status = 'A'

AND hca.account_number = ''

;

I put this condition "AND length (phone_number) > 40" but it is not working because it shows null fields, why?

Take a look at this:
IMAGE

Also, is this condition okay?

hcp ON hcas.party_site_id = hcp.owner_table_id 

Can you please help me with this?

PS: i am using SQL DEVELOPER and oracle database 11g

The way you modified the query doesn’t look good. You added a WHERE clause to a subquery taht is used to recover the user email and phone number from table hz_contact_point. Now it looks like the subquery does not return anything, and related’fields are NULL in the results (if you were using INNER JOIN instead of LEFT JOIN, you would have no results at all),

If you just need to control or format your output, don’t modify the subquery : it might break the logic. Use the main query. For example, the below sample replaces the phone number with NULL if it has more than 40 chars.

SELECT

     hp.party_name

    , hca.account_number

    ...

    , CASE WHEN LENGTH(hcp.phone_number) > 40 THEN NULL ELSE hcp.phone_number END

    , hcp.email_address

...

You left join that subquery aliased hcp, so if there are no matching rows from hcp for some rows of the other tables inner joined before, the values from hcp are NULL for these rows.

But if phone_number is declared as varchar2(40) you won't find any phone numbers with length(phone_number) > 40. They would have been rejected if some one tried to insert them.