2

I have a CDS View with multiple associations:

define view ZORDER as select from ZORDERHDR as orderHdr
association [0..1] to ZORDER_LOCATION as _location on _location.orderID = orderHdr.orderID
association [0..*] to ZORDER_ITEM as _items on _items.orderID = orderHdr.orderID
association [0..*] to ZORDER_PARTNER as _partners on _partners.orderID = orderHdr.orderID

Now, I would like to select from the view a single order and get a result something like this:

TYPES: BEGIN OF t_order,
         header TYPE zorderhdr,
         location TYPE zorder_location,
         items TYPE zorder_items, "This is a table type
        END OF t_order.

Is there a SELECT statement that can read all the order into a nested structure like the one above?

Edit: I added a second 0..* association to ensure that the proposed solution doesn't suggest to select everything and then reduce the header and location to their structures. The problem I see is that with complex CDS views I seem to need to handle all the associations with custom code like I would do if it was normal OpenSQL, then I lose the benefit of the CDS views model design (when not consuming from Gateway) and I select too much data.

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
RaTiO
  • 979
  • 2
  • 17
  • 33
  • Are you consuming the data using a OData service? If yes, you could use the `$expand` parameter to only fill `zorder_items`. My guess is, that debugging the relevant autogenerated DPC methods might be helpful. – koks der drache Sep 03 '19 at 06:56
  • Hi Konstantin. No, actually the question point is how to benefit from the CDS model when you consume it directly in ABAP using OpenSQL. – RaTiO Sep 03 '19 at 07:07

2 Answers2

2

Unfortunately, no.

I think you can address all fields of _items with _items~* in your select clause, but it's like a flat regular JOIN.

(Sidenote: your concept would map nicely to ABAP meshes if it were supported)

Pilot
  • 441
  • 2
  • 9
  • 1
    OK, thanks. I will wait a bit before accepting your answer, just in case someone else has a proposal. Regarding the meshes, yes, actually what I would expect is that you can generate an ABAP mesh from a CDS view with associations after you select with Open SQL – RaTiO Sep 03 '19 at 07:10
2

It is not possible to do SELECT like this. If you right click the CDS view, and click "Show SQL CREATE Statement", you should see it as a regular database SQL view. and it is a JOIN if you expose the fields from your association. You open the @AbapCatalog.sqlViewName: 'YOURSQLVIEW' by SE11, it is a flat structure.

One of the benefit of CDS view modeling with ASSOCIATION instead of JOIN is that you have the navigation generated if you include the CDS as datasource to your Gateway project. then you can have the URL navigation like zorderhdr/to_item to get all the items or like zorderhdr/to_item(item_key) to get one item.

One more thing, for transactional CDS view, you can generate a BOPF object by modeling the node hierarchy using ASSOCIATION .

Suncatcher
  • 10,355
  • 10
  • 52
  • 90
Haojie
  • 5,665
  • 1
  • 15
  • 14
  • Thanks for the answer, I know the view ends up being a flat structure, but I was hoping there was a way to simulate the full or partial tree navigation with a SELECT statement, I think SAP could make it possible. I'm aware of the BOPF generation but I couldn't try it properly since in 7.50 the BOPF framework was giving me a lot of problems, even though I see the blog you pointed is actually for 7.50. Now I'm curious how the BOPF does the selection of a single BO with all it's child associations, maybe I could use the idea – RaTiO Sep 03 '19 at 11:57
  • i have deep experience with CDS based BOPF. in case any question, please ask in SO. Thanks. – Haojie Sep 06 '19 at 13:09