Revision 50396
Added by Claudio Atzori about 6 years ago
queryOrganizations.sql | ||
---|---|---|
1 |
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 |
|
1 | 72 |
SELECT |
2 |
o.id AS organizationid,
|
|
3 |
o.legalshortname AS legalshortname,
|
|
4 |
o.legalname AS legalname,
|
|
5 |
o.websiteurl AS websiteurl,
|
|
6 |
o.logourl AS logourl,
|
|
7 |
o.ec_legalbody AS eclegalbody,
|
|
8 |
o.ec_legalperson AS eclegalperson,
|
|
9 |
o.ec_nonprofit AS ecnonprofit,
|
|
10 |
o.ec_researchorganization AS ecresearchorganization,
|
|
11 |
o.ec_highereducation AS echighereducation,
|
|
12 |
o.ec_internationalorganizationeurinterests AS ecinternationalorganizationeurinterests,
|
|
13 |
o.ec_internationalorganization AS ecinternationalorganization,
|
|
14 |
o.ec_enterprise AS ecenterprise,
|
|
15 |
o.ec_smevalidated AS ecsmevalidated,
|
|
16 |
o.ec_nutscode AS ecnutscode,
|
|
17 |
o.dateofcollection AS dateofcollection,
|
|
18 |
o.inferred AS inferred,
|
|
19 |
o.deletedbyinference AS deletedbyinference,
|
|
20 |
o.trust AS trust,
|
|
21 |
o.inferenceprovenance AS inferenceprovenance,
|
|
22 |
dc.id AS collectedfromid,
|
|
23 |
dc.officialname AS collectedfromname,
|
|
73 |
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,
|
|
24 | 95 |
|
25 |
cc.code || '@@@' || cc.name || '@@@' || cs.code || '@@@' || cs.name AS country,
|
|
26 |
pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name AS provenanceaction,
|
|
96 |
o.country || '@@@' || o.country || '@@@dnet:countries@@@dnet:countries' AS country,
|
|
97 |
'sysimport:crosswalk:entityregistry@@@sysimport:crosswalk:entityregistry@@@dnet:provenanceActions@@@dnet:provenanceActions' AS provenanceaction,
|
|
27 | 98 |
|
28 |
array_agg(DISTINCT i.pid || '###' || i.issuertypeclass) AS pid |
|
99 |
'{NULL}'::text[] AS pid |
|
100 |
FROM dsm_organizations o |
|
101 |
LEFT OUTER JOIN dsm_datasources d ON (d.id = o.collectedfrom); |
|
29 | 102 |
|
30 |
FROM organizations o |
|
31 |
LEFT OUTER JOIN class cc ON (cc.code = o.countryclass) |
|
32 |
LEFT OUTER JOIN scheme cs ON (cs.code = o.countryscheme) |
|
33 | 103 |
|
34 |
LEFT OUTER JOIN class pac ON (pac.code = o.provenanceactionclass) |
|
35 |
LEFT OUTER JOIN scheme pas ON (pas.code = o.provenanceactionscheme) |
|
36 | 104 |
|
37 |
LEFT OUTER JOIN organizationpids op ON (op.organization = o.id) |
|
38 |
LEFT OUTER JOIN identities i ON (i.pid = op.pid) |
|
39 | 105 |
|
40 |
LEFT OUTER JOIN datasources dc ON (dc.id = o.collectedfrom) |
|
41 | 106 |
|
42 |
GROUP BY |
|
43 |
o.id, |
|
44 |
o.legalshortname, |
|
45 |
o.legalname, |
|
46 |
o.websiteurl, |
|
47 |
o.logourl, |
|
48 |
o.ec_legalbody, |
|
49 |
o.ec_legalperson, |
|
50 |
o.ec_nonprofit, |
|
51 |
o.ec_researchorganization, |
|
52 |
o.ec_highereducation, |
|
53 |
o.ec_internationalorganizationeurinterests, |
|
54 |
o.ec_internationalorganization, |
|
55 |
o.ec_enterprise, |
|
56 |
o.ec_smevalidated, |
|
57 |
o.ec_nutscode, |
|
58 |
o.dateofcollection, |
|
59 |
o.inferred, |
|
60 |
o.deletedbyinference, |
|
61 |
o.trust, |
|
62 |
o.inferenceprovenance, |
|
63 |
dc.id, |
|
64 |
dc.officialname, |
|
65 |
cc.code, cc.name, cs.code, cs.name, |
|
66 |
pac.code, pac.name, pas.code, pas.name |
|
67 |
|
Also available in: Unified diff
read organizations from both old and new tables