truiz

--select * from document
--where id = 144722

SELECT --*
  document.documentum_id,
  'ARXEION',
  document.archive_id,
  to_char(document.data_creation, 'YYYY') as ANNO,
  to_char(document.data_creation, 'MONTH') as MESE,
  document.document_guid,
  document.file_name,
  document.document_version,
  document.current_version,
  NULL AS ATTACHMENTS,
  NULL AS  attachment_file_name
--  archive_name,
--  archive_location
FROM
  archive INNER JOIN document ON archive.id = document.archive_id
  --LEFT OUTER JOIN document_in_attachment ON document.id = document_in_attachment.document_id
  --LEFT OUTER JOIN attachment ON attachment.id = document_in_attachment.attachment_id
where document_guid = '96a63ffd-62f6-4a9e-8fda-d523ce31ca47'

UNION

SELECT --*
  document.documentum_id,
  'ARXEION',
  document.archive_id,
  to_char(document.data_creation, 'YYYY') as ANNO,
  to_char(document.data_creation, 'MONTH') as MESE,
  document.document_guid,
  document.file_name,
  document.document_version,
  document.current_version,
  CASE WHEN attachment.attachment_file_name IS NULL THEN NULL ELSE 'ATTACHMENTS' END AS ATTACHMENTS,
  attachment.attachment_file_name
--  archive_name,
--  archive_location
FROM
  archive INNER JOIN document ON archive.id = document.archive_id
  LEFT OUTER JOIN document_in_attachment ON document.id = document_in_attachment.document_id
  LEFT OUTER JOIN attachment ON attachment.id = document_in_attachment.attachment_id
where document_guid = '96a63ffd-62f6-4a9e-8fda-d523ce31ca47'