Project

General

Profile

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

    
(7-7/10)