correlated subquery in CDS view

[sunilsankar]

Hi All,

Is it possible to write a subquery in ABAP CDS view?

Here is the scenario:

SELECT f1, f2, f3...

   INTO CORRESPONDING FIELDS OF TABLE @lt_data

   FROM table 1 AS p

   INNER JOIN table 2 AS w

     ON  w~f1 = p~f1

     AND w~f2 = p~f2 )

          WHERE p~f1 IN @sp$00001

            AND p~f2 IN @sp$00002

            AND w~f3 = ( SELECT MAX( XXX ) FROM table 2 WHERE cond)

Appreciate your inputs on this. Thanks

[sunilsankar]

Any idea of this?

[former_member205436]

Hi,

as mentioned by Gunnlaugur, subselects cannot yet be implemented in ABAP CDS views. If you want to implement this in ABAP CDS, then "view on view" is the correct approach.

Another important aspect which you should consider is if this query must be implemented as a CDS view or if it is not better suited as an Open SQL statement. If you don't require the view for re-use purposes or for exposing as an OData service or for other consumption tools like ALV IDA, then Open SQL (which supports subselects) would be the better approach.

Kind regards

Chris

[sunilsankar]

Hi Edward,

I have modified code to use open sql and removed CDS approach as it is not really suitable in my case. Thanks,

Regards,

Sunil Sankar

[gunnlaugurth_einarsson]

I've not seen any signs of nested SQL in CDS views.

You can accomplish this with two views.

1. Selecting MAX(value)

2. Your scenarion using inner join to first CDS view