Bdtjtk

I have a table and I´d like to know what is the maximum lengh value that exist on the field "phone_number" and email_address.

I want to know if there exist any value that was entered that is greater than the allowed.

This is my 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')

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

;

The maximum length of a field(s):

SELECT MAX(LENGTH(email_address)), MAX(LENGTH(phone_number)) FROM hz_contact_points

No group by is needed because you're aggregating the whole set. Note that this tells you the longest data known (eg it will return 72) but it tells you nothing else. You would then have to query again to find rows where this length was the case:

SELECT * 

FROM hz_contact_points 

WHERE LENGTH(email_address) = 72

It might be easier to look at eg the top 10 rows ordered by email length descending:

SELECT * FROM (

  SELECT * 

  FROM hz_contact_points 

  ORDER BY LENGTH(email_address) DESC

) a

WHERE rownum <= 10

(If you're on oracle12c+ you can do away with the outer query and write FETCH FIRST 10 ROWS ONLY )

Rows that have more than one email address:

SELECT * 

FROM hz_contact_points 

WHERE email_address LIKE '%@%@%'

If you would like to find the rows that have the maximum field length in given field of a table, then try the following query.

SELECT *

FROM TABLE_NAME

WHERE LENGTH(FIELD_NAME) =

(

SELECT MAX(LENGTH(FIELD_NAME))

FROM TABLE_NAME

)