Code:
SELECT distinct
item.pitem_id ItemID,
itemrevision.pitem_revision_id Aenderungsstand,
itemrevision.psequence_id SequendeID,workspaceobject.pobject_type ObjektTyp,
statusRelation.pname Freigabestatus,
imanvolumeRelation.pwnt_path_name VolumePfad,
imanfileRelation.psd_path_name Unterordner,
imanfileRelation.pfile_name VolumeName,
imanfileRelation.poriginal_file_name Dateiname,
-----------------------------------------------------
revisionAnchor.pseq RevisionAnchorSeq,
imanvolumeRevisionAnchor.pwnt_path_name VolumePfad,
imanfileRevisionAnchor.psd_path_name Unterordner,
imanfileRevisionAnchor.pfile_name VolumeNameRevisionAnchor,
imanfileRevisionAnchor.poriginal_file_name Dateiname
FROM infodba.pitem item
join infodba.pitemrevision itemrevision
on item.puid = itemrevision.ritems_tagu
join infodba.pimanrelation relation
on itemrevision.puid = relation.rprimary_objectu
join infodba.pdataset dataset
on relation.rsecondary_objectu = dataset.puid
join infodba.pref_list_0 reflistRelation
on dataset.puid = reflistRelation.puid
JOIN infodba.pimanfile imanfileRelation
ON reflistRelation.pvalu_0 = imanfileRelation.puid
JOIN infodba.pimanvolume imanvolumeRelation
ON imanfileRelation.rvolume_tagu = imanvolumeRelation.puid
join infodba.pworkspaceobject workspaceobject
on dataset.puid = workspaceobject.puid
LEFT JOIN infodba.prelease_status_list_0 statuslistRelation
ON workspaceobject.puid = statuslistRelation.puid
LEFT JOIN infodba.preleasestatus statusRelation
ON statuslistRelation.pvalu_0 = statusRelation.puid
-------------------------------------------------------------------
join infodba.previsions_0 revisionAnchor
on dataset.rrev_chain_anchoru = revisionAnchor.puid
join infodba.pref_list_0 reflistRevisionAnchor
on revisionanchor.pvalu_0 = reflistrevisionanchor.puid
join infodba.pimanfile imanfileRevisionAnchor
on reflistrevisionanchor.pvalu_0 = imanfilerevisionanchor.puid
JOIN infodba.pimanvolume imanvolumeRevisionAnchor
ON imanfileRevisionAnchor.rvolume_tagu = imanvolumeRevisionAnchor.puid
LEFT JOIN infodba.prelease_status_list_0 statuslistRevisionAnchor
ON revisionanchor.pvalu_0 = statuslistRevisionAnchor.puid
LEFT JOIN infodba.preleasestatus statusRevisionAnchor
ON statuslistRevisionAnchor.pvalu_0 = statusRevisionAnchor.puid
where upper(item.pitem_id) = upper(ITEM ID')
and upper(workspaceobject.pobject_type) = upper('DATASET TYPE');