1 |
57383
|
michele.ar
|
SELECT
|
2 |
|
|
o.id AS organizationid,
|
3 |
|
|
coalesce((array_agg(a.acronym))[1], o.name) AS legalshortname,
|
4 |
|
|
o.name AS legalname,
|
5 |
|
|
array_agg(DISTINCT n.name) AS "alternativeNames",
|
6 |
|
|
(array_agg(u.url))[1] AS websiteurl,
|
7 |
|
|
o.modification_date AS dateoftransformation,
|
8 |
|
|
false AS inferred,
|
9 |
|
|
false AS deletedbyinference,
|
10 |
|
|
0.95 AS trust,
|
11 |
|
|
'' AS inferenceprovenance,
|
12 |
|
|
'openaire____::openorgs' AS collectedfromid,
|
13 |
|
|
'OpenOrgs Database' AS collectedfromname,
|
14 |
|
|
o.country || '@@@dnet:countries' AS country,
|
15 |
|
|
'sysimport:crosswalk:entityregistry@@@sysimport:crosswalk:entityregistry@@@dnet:provenance_actions@@@dnet:provenance_actions' AS provenanceaction,
|
16 |
|
|
array_agg(DISTINCT i.otherid || '###' || i.type) AS pid
|
17 |
|
|
FROM organizations o
|
18 |
|
|
LEFT OUTER JOIN acronyms a ON (a.id = o.id)
|
19 |
|
|
LEFT OUTER JOIN urls u ON (u.id = o.id)
|
20 |
|
|
LEFT OUTER JOIN other_ids i ON (i.id = o.id)
|
21 |
|
|
LEFT OUTER JOIN other_names n ON (n.id = o.id)
|
22 |
|
|
GROUP BY
|
23 |
|
|
o.id,
|
24 |
|
|
o.name,
|
25 |
|
|
o.modification_date,
|
26 |
|
|
o.country
|
27 |
|
|
|