Thanks Paul.
I was hesitant to post my full query. It is a long and complicated
query. But here it is now:
WITH p AS (
SELECT
ARRAY [ ARRAY [ 't',
'http://scientific.thomsonreuters.com/schema/wok5.4/public/FullRecord'
] ] AS ns),
uts AS (
SELECT
s.ut
FROM
wos_source.core_2015 s
WHERE
s.id BETWEEN 999900
AND 100000
),
utsb AS (
SELECT
b.ut
FROM
wos_2017_1.belongs2 b,
uts
WHERE
b.ut = uts.ut), q AS (
SELECT
s.ut,
unnest (xpath
('//t:static_data/t:fullrecord_metadata/t:addresses/t:address_name/t:address_spec',
xml,
ns)) AS address_spec
FROM
p,
uts a
LEFT JOIN utsb b ON b.ut = a.ut
LEFT JOIN wos_source.core_2015 s ON s.ut = a.ut
WHERE
b.ut IS NULL), r AS (
SELECT
s.ut,
unnest (xpath
('//t:static_data/t:item/t:reprint_contact/t:address_spec',
xml,
ns)) AS raddress_spec
FROM
p,
wos_2017_1.publication l,
uts a
LEFT JOIN utsb b ON b.ut = a.ut
LEFT JOIN wos_source.core_2015 s ON s.ut = a.ut
WHERE
b.ut IS NULL
AND xpath_exists
('//t:static_data/t:item/t:reprint_contact/t:address_spec', xml, ns)
AND s.ut = l.ut
AND l.pubyear < 1998), qd AS (
SELECT
DISTINCT ut,
CASE
WHEN xpath_exists ('//t:address_spec/t:country/text()',
q.address_spec,
p.ns)
THEN unnest (xpath ('//t:address_spec/t:country/text()',
q.address_spec,
p.ns))::citext
ELSE NULL
END country,
CASE
WHEN xpath_exists ('//t:address_spec/t:city/text()',
q.address_spec,
p.ns)
THEN unnest (xpath ('//t:address_spec/t:city/text()',
q.address_spec,
p.ns))::citext
ELSE NULL
END city,
CASE
WHEN xpath_exists ('//t:organizations/t:organization/text()',
q.address_spec,
ns)
THEN unnest (xpath ('//t:organizations/t:organization/text()',
q.address_spec,
ns))::citext
ELSE NULL
END organisation,
CASE
WHEN xpath_exists
('//t:organizations/t:organization[@pref="Y"]/text()',
q.address_spec,
ns)
THEN unnest (xpath
('//t:organizations/t:organization[@pref="Y"]/text()',
q.address_spec,
ns))::citext
ELSE NULL
END AS prefname,
CASE
WHEN xpath_exists ('//t:suborganizations/t:suborganization/text()',
q.address_spec,
ns)
THEN unnest (xpath ('//t:suborganizations/t:suborganization/text()',
q.address_spec,
ns))::citext
ELSE NULL
END suborgname,
CASE
WHEN xpath_exists ('/t:address_spec/@addr_no',
q.address_spec,
ns)
THEN (xpath ('/t:address_spec/@addr_no',
q.address_spec,
ns))
[ 1 ]::text::INTEGER
ELSE NULL
END addr_no
FROM
p,
q),
rd AS (
SELECT
DISTINCT ut,
CASE
WHEN xpath_exists ('//t:address_spec/t:country/text()',
r.raddress_spec,
p.ns)
THEN unnest (xpath ('//t:address_spec/t:country/text()',
r.raddress_spec,
p.ns))::citext
ELSE NULL
END country,
CASE
WHEN xpath_exists ('//t:address_spec/t:city/text()',
r.raddress_spec,
p.ns)
THEN unnest (xpath ('//t:address_spec/t:city/text()',
r.raddress_spec,
p.ns))::citext
ELSE NULL
END city,
CASE
WHEN xpath_exists ('//t:organizations/t:organization/text()',
r.raddress_spec,
ns)
THEN unnest (xpath ('//t:organizations/t:organization/text()',
r.raddress_spec,
ns))::citext
ELSE NULL
END organisation,
CASE
WHEN xpath_exists
('//t:organizations/t:organization[@pref="Y"]/text()',
r.raddress_spec,
ns)
THEN unnest (xpath
('//t:organizations/t:organization[@pref="Y"]/text()',
r.raddress_spec,
ns))::citext
ELSE NULL
END AS prefname,
CASE
WHEN xpath_exists ('//t:suborganizations/t:suborganization/text()',
r.raddress_spec,
ns)
THEN unnest (xpath ('//t:suborganizations/t:suborganization/text()',
r.raddress_spec,
ns))::citext
ELSE NULL
END suborgname,
CASE
WHEN xpath_exists ('/t:address_spec/@addr_no',
r.raddress_spec,
ns)
THEN (xpath ('/t:address_spec/@addr_no',
r.raddress_spec,
ns))
[ 1 ]::text::INTEGER
ELSE NULL
END reprint_addr_no
FROM
p,
r
WHERE
r.raddress_spec IS NOT NULL), uq AS (
SELECT
DISTINCT qd.ut,
qd.addr_no::INTEGER,
0 AS reprint_addr_no,
c.uuid city_id,
y.uuid country_id,
o.uuid organisation_id,
u.uuid suborg_id,
p.uuid pref_name_id
FROM
qd
LEFT JOIN wos_2017_1.city c ON (c.city = qd.city)
LEFT JOIN wos_2017_1.country_alias y ON (y.country = qd.country)
LEFT JOIN wos_2017_1.organisation o ON (o.organisation = regexp_replace (
regexp_replace (
regexp_replace (qd.organisation, '<', '<', 'g'),
'&', '&', 'g'),
'>', '>', 'g'))
LEFT JOIN wos_2017_1.org_pref_name p ON (p.name = qd.prefname)
LEFT JOIN wos_2017_1.suborg u ON (u.suborgname = regexp_replace (
regexp_replace (
regexp_replace (qd.suborgname, '<', '<', 'g'),
'&', '&', 'g'),
'>', '>', 'g'))),
ur AS (
SELECT
DISTINCT rd.ut,
0 AS addr_no,
rd.reprint_addr_no::INTEGER,
c.uuid city_id,
y.uuid country_id,
o.uuid organisation_id,
u.uuid suborg_id,
p.uuid pref_name_id
FROM
r,
rd
LEFT JOIN wos_2017_1.city c ON (c.city = rd.city)
LEFT JOIN wos_2017_1.country_alias y ON (y.country = rd.country)
LEFT JOIN wos_2017_1.organisation o ON (o.organisation = regexp_replace (
regexp_replace (
regexp_replace (rd.organisation, '<', '<', 'g'),
'&', '&', 'g'),
'>', '>', 'g'))
LEFT JOIN wos_2017_1.org_pref_name p ON (p.name = rd.prefname)
LEFT JOIN wos_2017_1.suborg u ON (u.suborgname = regexp_replace (
regexp_replace (
regexp_replace (rd.suborgname, '<', '<', 'g'),
'&', '&', 'g'),
'>', '>', 'g'))
WHERE
r.raddress_spec IS NOT NULL), qr AS (
SELECT
*
FROM
uq
UNION
SELECT
ur.*
FROM
ur)
SELECT
DISTINCT ON (qr.ut,
qr.addr_no,
qr.reprint_addr_no,
a.uuid,
qr.organisation_id,
qr.suborg_id,
qr.pref_name_id)
qr.ut,
qr.addr_no,
qr.reprint_addr_no,
a.uuid AS address_id,
qr.organisation_id,
qr.suborg_id,
qr.pref_name_id,
uuid_generate_v1 ()
uuid
FROM
qr
LEFT JOIN wos_2017_1.address a ON (a.city_id = qr.city_id
AND a.country_id = qr.country_id)
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
