| 1 |
26600
|
sandro.lab
|
select
|
| 2 |
|
|
c.creation as dri_objidentifier,
|
| 3 |
|
|
r.title as dc_title,
|
| 4 |
|
|
array_agg(DISTINCT lpad('0'||aut.rank,5,'0')||'ยงยงยง'||ltrim(replace(per.surname||', '||per.name,'UNKNOWN, ',''), ', ')) as dc_creator,
|
| 5 |
|
|
r.description as dc_description,
|
| 6 |
|
|
r.publisher as dc_publisher,
|
| 7 |
|
|
ARRAY(select trim(s) from unnest(string_to_array(r.keywords, ';')) as s) as dc_subject,
|
| 8 |
|
|
r.language as dc_language,
|
| 9 |
|
|
array_agg(DISTINCT ws.file_url) as dc_identifier,
|
| 10 |
|
|
r.publication_date as "dc_dateAccepted",
|
| 11 |
|
|
extract(year from r.publication_date)::text as oaf_publicationyear,
|
| 12 |
|
|
r.embargo_end_date as oaf_embargoenddate,
|
| 13 |
|
|
extract(year from r.embargo_end_date)::text as oaf_embargoendyear,
|
| 14 |
|
|
r.access_mode as oaf_accessrights,
|
| 15 |
|
|
r.haskind as oaf_haskind,
|
| 16 |
|
|
r.hastype as "dr_CobjCategory",
|
| 17 |
|
|
r.storagedate as oaf_storagedate,
|
| 18 |
|
|
r.createdfrom as temp_createdfrom,
|
| 19 |
|
|
c.creator as action_user,
|
| 20 |
|
|
c.date_of_creation as action_date_of_creation,
|
| 21 |
|
|
c.last_update_date as action_update_date,
|
| 22 |
|
|
|
| 23 |
|
|
array_agg(DISTINCT o.country_of_origin) as oaf_countryaffiliation,
|
| 24 |
|
|
array_agg(DISTINCT o.legal_short_name) as oaf_affiliationname,
|
| 25 |
|
|
array_agg(DISTINCT o.organizationid) as oaf_affiliationid,
|
| 26 |
|
|
array_agg(DISTINCT p.projectid) as temp_projectid,
|
| 27 |
|
|
|
| 28 |
|
|
array_agg(DISTINCT d.datasourceid) as temp_hostedbyid,
|
| 29 |
|
|
array_agg(DISTINCT d.official_name) as temp_hostedbyname,
|
| 30 |
|
|
|
| 31 |
|
|
array_agg(DISTINCT df.datasourceid) as temp_collectedfromid,
|
| 32 |
|
|
array_agg(DISTINCT df.official_name) as temp_collectedfromname
|
| 33 |
|
|
|
| 34 |
|
|
from
|
| 35 |
|
|
creations c
|
| 36 |
|
|
left outer join results r on (r.resultid = c.creation)
|
| 37 |
|
|
left outer join results_projects rp on (r.resultid = rp.result)
|
| 38 |
|
|
left outer join projects p on (p.projectid = rp.project)
|
| 39 |
|
|
left outer join authorships aut on (r.resultid = aut.result)
|
| 40 |
|
|
left outer join persons per on (per.personid = aut.author)
|
| 41 |
|
|
left outer join organizations o on (o.organizationid = aut.affiliation)
|
| 42 |
|
|
left outer join instances i on (r.resultid = i.resource)
|
| 43 |
|
|
left outer join webresources_instances wsi on (wsi.instanceid = i.instanceid)
|
| 44 |
|
|
left outer join webresources ws on (ws.webresourceid = wsi.webresourceid)
|
| 45 |
|
|
left outer join datasources d on (i.hostedby = d.datasourceid)
|
| 46 |
|
|
left outer join datasources df on (i.collectedFrom = df.datasourceid)
|
| 47 |
|
|
where
|
| 48 |
|
|
r.pendingforclaim = false
|
| 49 |
|
|
|
| 50 |
|
|
group by
|
| 51 |
|
|
c.creation, c.creator,
|
| 52 |
|
|
r.resultid, r.title, r.description, r.publisher, r.keywords, r.language,
|
| 53 |
|
|
r.publication_date, r.embargo_end_date, r.access_mode, r.haskind, r.hastype,
|
| 54 |
|
|
r.createdfrom , r.storagedate, c.creator, c.last_update_date, c.date_of_creation
|