1 |
50489
|
michele.ar
|
CREATE OR REPLACE VIEW orgs AS (
|
2 |
|
|
SELECT
|
3 |
|
|
o.id AS organizationid,
|
4 |
|
|
o.legalshortname AS legalshortname,
|
5 |
|
|
o.legalname AS legalname,
|
6 |
|
|
o.websiteurl AS websiteurl,
|
7 |
|
|
o.logourl AS logourl,
|
8 |
|
|
o.ec_legalbody AS eclegalbody,
|
9 |
|
|
o.ec_legalperson AS eclegalperson,
|
10 |
|
|
o.ec_nonprofit AS ecnonprofit,
|
11 |
|
|
o.ec_researchorganization AS ecresearchorganization,
|
12 |
|
|
o.ec_highereducation AS echighereducation,
|
13 |
|
|
o.ec_internationalorganizationeurinterests AS ecinternationalorganizationeurinterests,
|
14 |
|
|
o.ec_internationalorganization AS ecinternationalorganization,
|
15 |
|
|
o.ec_enterprise AS ecenterprise,
|
16 |
|
|
o.ec_smevalidated AS ecsmevalidated,
|
17 |
|
|
o.ec_nutscode AS ecnutscode,
|
18 |
|
|
o.dateofcollection AS dateofcollection,
|
19 |
|
|
o.inferred AS inferred,
|
20 |
|
|
o.deletedbyinference AS deletedbyinference,
|
21 |
|
|
o.trust AS trust,
|
22 |
|
|
o.inferenceprovenance AS inferenceprovenance,
|
23 |
|
|
dc.id AS collectedfromid,
|
24 |
|
|
dc.officialname AS collectedfromname,
|
25 |
|
|
|
26 |
|
|
cc.code || '@@@' || cc.name || '@@@' || cs.code || '@@@' || cs.name AS country,
|
27 |
|
|
pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name AS provenanceaction,
|
28 |
|
|
|
29 |
|
|
array_agg(DISTINCT i.pid || '###' || i.issuertypeclass) AS pid
|
30 |
|
|
|
31 |
|
|
FROM organizations o
|
32 |
|
|
LEFT OUTER JOIN class cc ON (cc.code = o.countryclass)
|
33 |
|
|
LEFT OUTER JOIN scheme cs ON (cs.code = o.countryscheme)
|
34 |
|
|
|
35 |
|
|
LEFT OUTER JOIN class pac ON (pac.code = o.provenanceactionclass)
|
36 |
|
|
LEFT OUTER JOIN scheme pas ON (pas.code = o.provenanceactionscheme)
|
37 |
|
|
|
38 |
|
|
LEFT OUTER JOIN organizationpids op ON (op.organization = o.id)
|
39 |
|
|
LEFT OUTER JOIN identities i ON (i.pid = op.pid)
|
40 |
|
|
|
41 |
|
|
LEFT OUTER JOIN datasources dc ON (dc.id = o.collectedfrom)
|
42 |
|
|
|
43 |
|
|
GROUP BY
|
44 |
|
|
o.id,
|
45 |
|
|
o.legalshortname,
|
46 |
|
|
o.legalname,
|
47 |
|
|
o.websiteurl,
|
48 |
|
|
o.logourl,
|
49 |
|
|
o.ec_legalbody,
|
50 |
|
|
o.ec_legalperson,
|
51 |
|
|
o.ec_nonprofit,
|
52 |
|
|
o.ec_researchorganization,
|
53 |
|
|
o.ec_highereducation,
|
54 |
|
|
o.ec_internationalorganizationeurinterests,
|
55 |
|
|
o.ec_internationalorganization,
|
56 |
|
|
o.ec_enterprise,
|
57 |
|
|
o.ec_smevalidated,
|
58 |
|
|
o.ec_nutscode,
|
59 |
|
|
o.dateofcollection,
|
60 |
|
|
o.inferred,
|
61 |
|
|
o.deletedbyinference,
|
62 |
|
|
o.trust,
|
63 |
|
|
o.inferenceprovenance,
|
64 |
|
|
dc.id,
|
65 |
|
|
dc.officialname,
|
66 |
|
|
cc.code, cc.name, cs.code, cs.name,
|
67 |
|
|
pac.code, pac.name, pas.code, pas.name
|
68 |
|
|
);
|
69 |
|
|
|
70 |
|
|
SELECT * FROM orgs
|
71 |
|
|
UNION
|
72 |
48139
|
alessia.ba
|
SELECT
|
73 |
50489
|
michele.ar
|
o.id AS organizationid,
|
74 |
|
|
o.legalshortname AS legalshortname,
|
75 |
|
|
o.legalname AS legalname,
|
76 |
|
|
o.websiteurl AS websiteurl,
|
77 |
|
|
o.logourl AS logourl,
|
78 |
|
|
null AS eclegalbody,
|
79 |
|
|
null AS eclegalperson,
|
80 |
|
|
null AS ecnonprofit,
|
81 |
|
|
null AS ecresearchorganization,
|
82 |
|
|
null AS echighereducation,
|
83 |
|
|
null AS ecinternationalorganizationeurinterests,
|
84 |
|
|
null AS ecinternationalorganization,
|
85 |
|
|
null AS ecenterprise,
|
86 |
|
|
null AS ecsmevalidated,
|
87 |
|
|
null AS ecnutscode,
|
88 |
|
|
o.dateofcollection AS dateofcollection,
|
89 |
|
|
false AS inferred,
|
90 |
|
|
false AS deletedbyinference,
|
91 |
|
|
0.9 AS trust,
|
92 |
|
|
'' AS inferenceprovenance,
|
93 |
|
|
d.id AS collectedfromid,
|
94 |
|
|
d.officialname AS collectedfromname,
|
95 |
26600
|
sandro.lab
|
|
96 |
50489
|
michele.ar
|
o.country || '@@@' || o.country || '@@@dnet:countries@@@dnet:countries' AS country,
|
97 |
|
|
'sysimport:crosswalk:entityregistry@@@sysimport:crosswalk:entityregistry@@@dnet:provenance_actions@@@dnet:provenance_actions' AS provenanceaction,
|
98 |
48139
|
alessia.ba
|
|
99 |
50489
|
michele.ar
|
ARRAY[]::text[] AS pid
|
100 |
|
|
FROM dsm_organizations o
|
101 |
|
|
LEFT OUTER JOIN dsm_datasources d ON (d.id = o.collectedfrom);
|
102 |
48139
|
alessia.ba
|
|
103 |
|
|
|
104 |
|
|
|
105 |
|
|
|