Project

General

Profile

« Previous | Next » 

Revision 57608

query to import OpenOrgs without acronyms

View differences:

queryOrganizationsFromOpenOrgsDB.sql
26 26
	o.country
27 27
	
28 28
UNION ALL
29
		
29
	
30 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 31
	'openorgsmesh'||substring(o.id, 13)||'-'||md5(n.name)                                                                         AS organizationid,
58 32
	n.name                                                                                                                        AS legalshortname,
59 33
	n.name                                                                                                                        AS legalname,

Also available in: Unified diff