20 lines
842 B
SQL
20 lines
842 B
SQL
CREATE OR REPLACE VIEW public.vw_utility_last_counterparty_address AS
|
|
WITH lastaddresses AS (
|
|
SELECT pa_1.id,
|
|
row_number() OVER (PARTITION BY pa_1.party ORDER BY pa_1.create_date DESC) AS rn
|
|
FROM party_address pa_1
|
|
WHERE (pa_1.active = true)
|
|
)
|
|
SELECT pa.id AS "intPartyAddressId",
|
|
pa.party AS "intPartyId",
|
|
COALESCE(TRIM(BOTH FROM pa.street), ''::text) AS "strStreet",
|
|
COALESCE(pa.postal_code, ''::character varying) AS "strPostalCode",
|
|
pa.city AS "strCity",
|
|
c.name AS "strCountry",
|
|
COALESCE(cr.name, 'Undefined'::character varying) AS "strRegion"
|
|
FROM (((party_address pa
|
|
JOIN lastaddresses la ON ((pa.id = la.id)))
|
|
JOIN country_country c ON ((pa.country = c.id)))
|
|
LEFT JOIN country_region cr ON ((c.region = cr.id)))
|
|
WHERE (pa.active = true);;
|