Revision 51175
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 | 1 |
SELECT * FROM orgs |
71 | 2 |
UNION |
72 | 3 |
SELECT |
Also available in: Unified diff
assume the view orgs is already there