1
|
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 || '@@@dnet:pid_types') 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
|
|
28
|
UNION ALL
|
29
|
|
30
|
SELECT
|
31
|
'openorgsmesh'||substring(o.id, 13)||'-'||md5(a.acronym) AS organizationid,
|
32
|
a.acronym AS legalshortname,
|
33
|
a.acronym AS legalname,
|
34
|
ARRAY[]::text[] AS "alternativeNames",
|
35
|
(array_agg(u.url))[1] AS websiteurl,
|
36
|
o.modification_date AS dateoftransformation,
|
37
|
false AS inferred,
|
38
|
false AS deletedbyinference,
|
39
|
0.88 AS trust,
|
40
|
'' AS inferenceprovenance,
|
41
|
'openaire____::openorgs' AS collectedfromid,
|
42
|
'OpenOrgs Database' AS collectedfromname,
|
43
|
o.country || '@@@dnet:countries' AS country,
|
44
|
'sysimport:crosswalk:entityregistry@@@sysimport:crosswalk:entityregistry@@@dnet:provenance_actions@@@dnet:provenance_actions' AS provenanceaction,
|
45
|
array_agg(DISTINCT i.otherid || '###' || i.type || '@@@dnet:pid_types') AS pid
|
46
|
FROM acronyms a
|
47
|
LEFT OUTER JOIN organizations o ON (a.id = o.id)
|
48
|
LEFT OUTER JOIN urls u ON (u.id = o.id)
|
49
|
LEFT OUTER JOIN other_ids i ON (i.id = o.id)
|
50
|
GROUP BY
|
51
|
o.id, o.modification_date, o.country, a.acronym
|
52
|
|
53
|
|
54
|
UNION ALL
|
55
|
|
56
|
SELECT
|
57
|
'openorgsmesh'||substring(o.id, 13)||'-'||md5(n.name) AS organizationid,
|
58
|
n.name AS legalshortname,
|
59
|
n.name AS legalname,
|
60
|
ARRAY[]::text[] AS "alternativeNames",
|
61
|
(array_agg(u.url))[1] AS websiteurl,
|
62
|
o.modification_date AS dateoftransformation,
|
63
|
false AS inferred,
|
64
|
false AS deletedbyinference,
|
65
|
0.88 AS trust,
|
66
|
'' AS inferenceprovenance,
|
67
|
'openaire____::openorgs' AS collectedfromid,
|
68
|
'OpenOrgs Database' AS collectedfromname,
|
69
|
o.country || '@@@dnet:countries' AS country,
|
70
|
'sysimport:crosswalk:entityregistry@@@sysimport:crosswalk:entityregistry@@@dnet:provenance_actions@@@dnet:provenance_actions' AS provenanceaction,
|
71
|
array_agg(DISTINCT i.otherid || '###' || i.type || '@@@dnet:pid_types') AS pid
|
72
|
FROM other_names n
|
73
|
LEFT OUTER JOIN organizations o ON (n.id = o.id)
|
74
|
LEFT OUTER JOIN urls u ON (u.id = o.id)
|
75
|
LEFT OUTER JOIN other_ids i ON (i.id = o.id)
|
76
|
GROUP BY
|
77
|
o.id, o.modification_date, o.country, n.name
|
78
|
|
79
|
|