ReportSQL - Client Full Address in One Cell

• Text formula field on Case Data table - does not need to be on the person address subtables
• Substitute "mailing" for "home" to ge the mailing address

(SELECT
  TRIM(
    CONCAT_WS(', ',
      NULLIF(TRIM(CONCAT_WS(' ',
           NULLIF(a.addr1, ''),
           NULLIF(a.addr2, ''),
           NULLIF(a.apt_num, '')
      )), ''),
      NULLIF(TRIM(a.city), ''),
      NULLIF(TRIM(
        CONCAT_WS(' ',
          a.state,
          CASE
            WHEN LENGTH(a.zip) = 9 AND a.zip ~ '^\d{9}$' THEN
              SUBSTR(a.zip,1,5) || '-' || SUBSTR(a.zip,6,4)
            ELSE
              a.zip
          END
        )
      ), '')
    )
)
FROM matter
JOIN person ON matter.person_id = person.id
JOIN person_address ON person.id = person_address.person_id
AND person_address.primary_address
AND address_type = (SELECT id FROM lookup_address_type WHERE note='home')
JOIN address AS a ON a.id = person_address.address_id
WHERE a.zip <> ''
and matter.id = %tableAlias%.id)