select
        r.resultid                                          as dri_objidentifier,
        r.title                                             as dc_title,
        array_agg(DISTINCT lpad('0'||aut.rank,5,'0')||'§§§'||ltrim(replace(per.surname||', '||per.name,'UNKNOWN, ',''), ', ')) as dc_creator,
      r.description                                       as dc_description,
        r.publisher                                         as dc_publisher,
        r.keywords                                          as dc_subject,
        r.language                                          as dc_language,
        array_agg(DISTINCT ws.file_url)                     as dc_identifier,
        r.publication_date                                  as oaf_publicationdate,
        r.embargo_end_date                                  as oaf_embargoenddate,
        r.access_mode                                       as oaf_accessmode,
        r.haskind                                           as oaf_haskind,
        r.hastype                                           as "dr_CobjCategory",
        r.storagedate                                           as oaf_storagedate,
        createdfrom                                         as createdfrom,
        array_agg(DISTINCT p.projectid)                     as oaf_projectid,
        array_agg(DISTINCT cr.creator)                              as creators

from

       results r
        left outer join results_projects rp on (r.resultid = rp.result)
        left outer join projects p on (p.projectid = rp.project)
        left outer join projects_projectsubjects ps on (ps.project = p.projectID)
        left outer join fundingschemes fs on (p.fundedhow = fs.fundingschemeid)
        left outer join programs s on (p.fundedby = s.programid)
        left outer join specificprograms sp on (s.hasspecificprogram = sp.specificprogramid)
        left outer join frameworkprograms fp on (sp.hasframeworkprogram = fp.frameworkprogramid)
        left outer join authorships aut on (r.resultid = aut.result)
        left outer join persons per on (per.personid = aut.author)
        left outer join organizations o on (o.organizationid = aut.affiliation)
        left outer join instances i on (r.resultid = i.resource)
        left outer join webresources_instances wsi on (wsi.instanceid = i.instanceid)
        left outer join webresources ws on (ws.webresourceid = wsi.webresourceid)

        join creations cr on cr.creation = r.resultid

where
        r.pmhdeleted != true and r.pendingforclaim = false

group by
        r.resultid, r.title, r.description, r.publisher, r.keywords, r.language,
        r.publication_date, r.embargo_end_date, r.access_mode, r.haskind, r.hastype,
        r.storagedate, r.createdfrom