Exposing data with joins in gateway

[derek_morin]

We have created a lot of GET_ENTITY_SET methods that do selects from views we create.

A confusion I keep hitting is dealing with joins.

As far as I know SAP only supports inner joins when defining a view. This means if I define a view that way and there is data that does not exist on the second table I will end up losing rows.

For example if I want a view with:

QPGR SAP Table - Inspection catalog code groups

Joined to

QPGT (Code Group Texts)

And I hard code the language to 'E'

If QPGT is missing any language translations for english those rows will be lost.

I'm finding the lack of "left outer join" on views to be very frustrating.

Another option is to not join to that table and then write custom code to retrieve the code group text if it exists.

If we go with that approach then the regular odata filtering will not be supported for KURZTEXT, which is also annoying ( but maybe better than losing data? )

How do people deal with these sorts of issues?

[derek_morin]

Here is an example of sql that simulates what an inner join ( on a view ) would do:

select 
count(*)
--KATALOGART,CODEGRUPPE,INAKTIV 
from QPGR where MANDT = 801

select 
--q.KATALOGART, q.CODEGRUPPE,q.INAKTIV, t.KURZTEXT 
count(*)
from QPGR q
inner join QPGT t on q.MANDT = t.MANDANT and q.KATALOGART = t.KATALOGART and q.CODEGRUPPE = t.CODEGRUPPE and t.SPRACHE = 'E'
where q.MANDT = 801

So I only have english for 276 / 285 rows.
===========
285
===========
276

[kammaje_cis]

The approach you are explaining is called as 'Inside Out' approach of Gateway service implementing, which I think that is not efficient. In my last 4-5 years of creating Gateway services, I have rarely done this way, but rather coded my DPC_EXT class.

I would suggest you to just manually create your entity with properties, generate runtime artefacts, and then goto *DPC_EXT class directly and code the operation you need.

[kammaje_cis]

As far as I know SAP only supports inner joins

I do not think this is right.

Check here.

https://help.sap.com/abapdocu_731/en/abapselect_join.htm

[derek_morin]

Sorry. I meant when we create a view. Our typical approach to exposing data has been to model a view, create an RFC that can pull data from the view ( we have a reusable one that can work with any view name ) and then create an entity set that pulls data from that view. But when we create a view ( in SE80 ) in our package, I think we can only create views with inner joins.