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

[ShubhamK · Accepted solution]

Dear Deborah,

For joining Activity & ServiceCall Table

Could you please try

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

This will give you all activities linked to this ServiceCall

Similarly, for TimeEffort, Milleages

Can you try

SELECT te,sc.id, ac.id,ac.code,sc.code FROM ServiceCall sc JOIN Activity ac ON ac.object=sc.id JOIN TimeEffort te ON te.object=ac.id WHERE sc.code='5521620';

SELECT m,sc.id, ac.id,ac.code,sc.code FROM ServiceCall sc JOIN Activity ac ON ac.object=sc.id JOIN Mileage m ON m.object=ac.id WHERE sc.code='5521620';

Hope this helps

Kind Regards,

Shubham

[DAcker · Accepted solution]

Thanks a lot. Yes, it is working! I tried it that way before, but my test data was not correct and afterwards I somehow was so focused on the IN function, that I didnt found the solution. Thanks again, it works. BR, Deborah

[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