How to get all the elements of an association?

Question

I have created a CDS views as follows:

define view YGAC_I_REQUEST_ROLE
  with parameters
    pm_req_id    : grfn_guid,
    @Consumption.defaultValue: 'ROL'
    pm_item_type : grac_prov_item_type,
    @Consumption.defaultValue: 'AP'
    pm_approval  : grac_approval_status
  as select from YGAC_I_REQ_PROVISION_ITEM as provitem

  association [1..1] to YGAC_I_ROLE as _Role on _Role.RoleId = provitem.ProvisionItemId

  association [1..*] to YGAC_I_ROLE_RS as _Relation on _Relation.RoleId1 = provitem.ProvisionItemId

{
  key ReqId,
  key ReqIdItem,
      Connector,
      ProvisionItemId,
      ActionType,
      ValidFrom,
      ValidTo,

      _Role.RoleId,
      _Role.RoleName,
      _Role.RoleType,


      _Role,
      _Relation
}
where
      ReqId             = $parameters.pm_req_id
  and ProvisionItemType = $parameters.pm_item_type
  and ApprovalStatus    = $parameters.pm_approval

Then I have consumed in ABAP:

  SELECT
    FROM ygac_i_request_role( pm_req_id = @lv_test,
                              pm_item_type = @lv_item_type,
                              pm_approval = @lv_approval
                            )
    FIELDS reqid,
           connector,
           provisionitemid
  INTO TABLE @DATA(lt_result).

How to get the list of _Relation according to selection above.

would it help? https://stackoverflow.com/questions/60996902/how-to-consume-a-cds-from-abap-sql-using-an-association-value-in-the-where-condi/61004459#61004459 — Haojie, May 29 '20 at 08:36
Does this answer your question? [How to consume a CDS from ABAP SQL using an association value in the WHERE condition?](https://stackoverflow.com/questions/60996902/how-to-consume-a-cds-from-abap-sql-using-an-association-value-in-the-where-condi) — Sandra Rossi, May 29 '20 at 10:04
@SandraRossi this question is not about WHERE clause but about the specifying association fields in the SELECT clause, so it is not actually a duplicate — Suncatcher, Jun 01 '20 at 09:34

Suncatcher · Answer 1 · 2020-06-01T09:47:30.737

This is generally not possible like in ABAP SQL queries:

SELECT m~*, kt~*
  FROM mara AS m
  JOIN makt AS kt
...

This contradicts the whole idea of CDS associations, because they were created to join on-demand and to reduce redundant calls to database. Fetching all fields negates the whole idea of "lazy join".

However, there is another syntax in FROM clause which is enabled by path expressions that allows querying underlining associations both fully and by separate elements. Here is how

SELECT *
  FROM ygac_i_request_role( pm_req_id = @lv_test )
       \_Role AS role
  INTO TABLE @DATA(lt_result).

This fetches all the fields of _Role association into internal table.

Note: remember, it is not possible to fetch all the published associations of current view simultaneously, only one path per query.

Possible workaround is to use JOIN

SELECT *
  FROM ygac_i_request_role AS main
  JOIN ygac_i_request_role
       \_Role AS role
    ON main~ProvisionItemId = role~RoleId
  JOIN ygac_i_request_role
       \_Relation AS relation
    ON main~ProvisionItemId = relation~RoleId1
  INTO TABLE @DATA(lt_table).

This creates deep-structured type with dedicated structure for every join association like this:

If you are not comfortable with such structure for you task, lt_table should be declared statically to put up all the fields in a flat way

TYPES BEGIN OF ty_table.
        INCLUDE TYPE ygac_i_request_role.
        INCLUDE TYPE ygac_i_role.
        INCLUDE TYPE ygac_i_role_rs.
TYPES END OF ty_table.

How to get all the elements of an association?

1 Answers1