Revision 48139
Added by Alessia Bardi almost 7 years ago
| queryOrganizations.sql | ||
|---|---|---|
| 1 |
SELECT o.id as organizationid, |
|
| 2 |
o.legalshortname as legalshortname, |
|
| 3 |
o.legalname as legalname, |
|
| 4 |
o.websiteurl as websiteurl, |
|
| 5 |
o.logourl as logourl, |
|
| 6 |
o.ec_legalbody as eclegalbody, |
|
| 7 |
o.ec_legalperson as eclegalperson, |
|
| 8 |
o.ec_nonprofit as ecnonprofit, |
|
| 9 |
o.ec_researchorganization as ecresearchorganization, |
|
| 10 |
o.ec_highereducation as echighereducation, |
|
| 11 |
o.ec_internationalorganizationeurinterests as ecinternationalorganizationeurinterests, |
|
| 12 |
o.ec_internationalorganization as ecinternationalorganization, |
|
| 13 |
o.ec_enterprise as ecenterprise, |
|
| 14 |
o.ec_smevalidated as ecsmevalidated, |
|
| 15 |
o.ec_nutscode as ecnutscode, |
|
| 16 |
o.dateofcollection as dateofcollection, |
|
| 17 |
o.inferred as inferred, |
|
| 18 |
o.deletedbyinference as deletedbyinference, |
|
| 19 |
o.trust as trust, |
|
| 20 |
o.inferenceprovenance as inferenceprovenance, |
|
| 21 |
dc.id as collectedfromid, |
|
| 22 |
dc.officialname as collectedfromname, |
|
| 23 |
|
|
| 24 |
cc.code || '@@@' || cc.name || '@@@' || cs.code || '@@@' || cs.name as country, |
|
| 25 |
pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name as provenanceaction, |
|
| 26 |
|
|
| 27 |
array_agg(DISTINCT i.pid || '###' || i.issuertypeclass) as pid |
|
| 1 |
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, |
|
| 28 | 24 |
|
| 25 |
cc.code || '@@@' || cc.name || '@@@' || cs.code || '@@@' || cs.name AS country, |
|
| 26 |
pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name AS provenanceaction, |
|
| 27 |
|
|
| 28 |
array_agg(DISTINCT i.pid || '###' || i.issuertypeclass) AS pid |
|
| 29 |
|
|
| 29 | 30 |
FROM organizations o |
| 30 |
left outer join class cc on (cc.code = o.countryclass)
|
|
| 31 |
left outer join scheme cs on (cs.code = o.countryscheme)
|
|
| 32 |
|
|
| 33 |
left outer join class pac on (pac.code = o.provenanceactionclass)
|
|
| 34 |
left outer join scheme pas on (pas.code = o.provenanceactionscheme)
|
|
| 35 |
|
|
| 36 |
left outer join organizationpids op on (op.organization = o.id)
|
|
| 37 |
left outer join identities i on (i.pid = op.pid)
|
|
| 38 |
|
|
| 39 |
left outer join datasources dc on (dc.id = o.collectedfrom)
|
|
| 40 |
|
|
| 31 |
LEFT OUTER JOIN class cc ON (cc.code = o.countryclass)
|
|
| 32 |
LEFT OUTER JOIN scheme cs ON (cs.code = o.countryscheme)
|
|
| 33 |
|
|
| 34 |
LEFT OUTER JOIN class pac ON (pac.code = o.provenanceactionclass)
|
|
| 35 |
LEFT OUTER JOIN scheme pas ON (pas.code = o.provenanceactionscheme)
|
|
| 36 |
|
|
| 37 |
LEFT OUTER JOIN organizationpids op ON (op.organization = o.id)
|
|
| 38 |
LEFT OUTER JOIN identities i ON (i.pid = op.pid)
|
|
| 39 |
|
|
| 40 |
LEFT OUTER JOIN datasources dc ON (dc.id = o.collectedfrom)
|
|
| 41 |
|
|
| 41 | 42 |
GROUP BY |
| 42 |
o.id,
|
|
| 43 |
o.legalshortname,
|
|
| 44 |
o.legalname,
|
|
| 45 |
o.websiteurl,
|
|
| 46 |
o.logourl,
|
|
| 47 |
o.ec_legalbody,
|
|
| 48 |
o.ec_legalperson,
|
|
| 49 |
o.ec_nonprofit,
|
|
| 50 |
o.ec_researchorganization,
|
|
| 51 |
o.ec_highereducation,
|
|
| 52 |
o.ec_internationalorganizationeurinterests,
|
|
| 53 |
o.ec_internationalorganization,
|
|
| 54 |
o.ec_enterprise,
|
|
| 55 |
o.ec_smevalidated,
|
|
| 56 |
o.ec_nutscode,
|
|
| 57 |
o.dateofcollection,
|
|
| 58 |
o.inferred,
|
|
| 59 |
o.deletedbyinference,
|
|
| 60 |
o.trust,
|
|
| 61 |
o.inferenceprovenance,
|
|
| 62 |
dc.id,
|
|
| 63 |
dc.officialname,
|
|
| 64 |
cc.code, cc.name, cs.code, cs.name,
|
|
| 65 |
pac.code, pac.name, pas.code, pas.name
|
|
| 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 |
|
| 66 | 67 |
|
Also available in: Unified diff
integrated (hopefully) all required changes from dnet40