SELECT into structured object with ABAP CDS associations

Question

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.

Answer 1

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 .