Hi Nicolai,
thanks for the answer.
That works if I want to query one object. My example query had the ID just set to illustrate the problem.
In my case I have a more generic approach. But your answer helped and I'm positive I now achive what I.
But I'm not sure if such a query is still performant if there are millions of objects.
What do you think, should this work or do you know a better approach?
SELECT u.sysname,
d1.sysid, d1.sysitemid, d1.sysversionnumber, d1.sysversionmostrecent, d1.fieldCondition,
d2.sysid, d2.sysitemid, d2.sysversionnumber, d2.fieldCondition
FROM dms:etcmdocument d1,
dms:etcmdocument (all versions) d2 join ecm:syscreationtraceowner2user c2u join org:sysuser u
WHERE (d1.fieldCondition = false or d1.fieldCondition = null)
and d1.sysitemid = d2.sysitemid
and d2.sysversionnumber = 1
and d1.sysversionmostrecent = true
and u.sysname in ('root', 'technical')
The query might even be more complex setting conditions on contextfolder:
SELECT u.sysname,
d1.sysid, d1.sysitemid, d1.sysversionnumber, d1.sysversionmostrecent, d1.fieldCondition,
d2.sysid, d2.sysitemid, d2.sysversionnumber, d2.fieldCondition,
f.sysid, f.sysitemid, f.sysversionnumber, f.sysversionmostrecent
FROM dms:sapalcomponent d1,
dms:sapalcomponent (all versions) d2 join ecm:syscreationtraceowner2user c2u join org:sysuser u,
dms:sapaldocument f
WHERE f isParentOf d1
and f.barcode in ('4711','4712')
and (d1.fieldCondition = false or d1.fieldCondition = null)
and d1.sysitemid = d2.sysitemid
and d2.sysversionnumber = 1
and d1.sysversionmostrecent = true
and u.sysname in ('root', 'technical')