SAP FSM - JOIN muliple activites to service call, Error CA-127 [REFERENCE in REFERENCE

[DAcker]

Hello experts, 

Can I join the activities from a Service Call in the Query API? 

I tried to JOIN with IN statement, but I get the error message CA-127: Cannot perform operation: [REFERENCE in REFERENCE].

SELECT sc.id, ac.id
FROM ServiceCall sc 
JOIN Activity ac ON sc.id IN ac.object.objectId
WHERE sc.code='5521620'

My aim is to get all timeeffort and mileage positions connected to a service call. As the timeefforts and mileages are in reference to the activites, I first need to join the activity table. 

Is this possible or not? 

Thanks a lot for your advice!

Best regards,
Deborah

#sapfsm #fsm #queryapi #join #activity #servicecall

[abhisheksap2023]

There is also a option to call a subQuery, try using that
e.g.
SELECT a.id, sas.name
FROM Activity a, ServiceAssignmentStatus sas,
(SELECT s.object.objectId AS objectId, MAX(s.createDateTime) AS maxcdt FROM ServiceAssignmentStatus s GROUP BY s.object.objectId) sas2
WHERE a.id = sas.object.objectId
AND sas2.objectId = a.id
AND sas.createDateTime = sas2.maxcdt