Does Service Layer support left join?

[former_member500538]

Hi,

I have two tables. The first one is Items table, the second one is a UDT that contains itemCode and some other columns. I want to do left join to get all records from Items table and related records from second table (if exists). How can I do that?

Thanks

Haktan

[former_member390407]

Hello Haktan,

If your question is how to achieve that I would recommend 2 approaches:

1) Call a view instead of a standard ServiceLayer entity. More info: https://blogs.sap.com/2018/02/01/new-sap-business-one-semantic-layer-view-exposure/

2) Call a procedure using XSJS (not ServiceLayer, but still HTTP REST). You need to implement your XS JS wrapper for the procedure or you can use the following: https://blogs.sap.com/2018/07/19/hana-procedure-performer-xs-js-project/

[ANKIT_CHAUHAN]

Hi Haktan Cinkilic,

You can opt to use Crossjoin for your purpose. Please do not get confused with the Database Joins that you might be thinking.

Refer to following thread:

Discussion

Kind regards,

ANKIT CHAUHAN

SAP Business One Support

[former_member500538]

Hi Ankit,

Thank you for your quick response. I checked the the documentation in other thread but I couldn't find a clear answer. I am using the crossjoin but it works like inner join. It would be perfect If you could help me to use it as a left join. Do you have any example?

Regards

Haktan

[ANKIT_CHAUHAN]

Hi Haktan Cinkilic,

As mentioned previously, do not confuse with Crossjoin and the joins that you use in SQL or HANA Studio.

Write the Left Join query in SAP HANA Studio and try to use that same query using Crossjoin in Service Layer.

Update: Just came to know that the Left Join is not supported yet.

Kind regards,

ANKIT CHAUHAN

SAP Business One Support