Rewrite select statement with union into cds view?

[former_member727610]

Hello,

So I got an odata service select. It works, but the problem is with the paging. This is the select:

   SELECT a915~lifnr AS vendor,<br>           a915~matnr AS erpnumber,<br>           a915~datbi AS validto,<br>           a915~datab AS validfrom,<br>           konp~kbetr AS freightprice,<br>           konp~konwa AS currency,<br>           konp~loevm_ko AS deleted,<br>           ' ' AS werks<br>           FROM a915 INNER JOIN konp ON a915~knumh = konp~knumh<br>           WHERE a915~kappl = @lc_appl AND a915~kschl = @lc_condition<br>           AND a915~lifnr IN @lt_params_lifnr<br>           AND a915~matnr IN @lt_params_matnr
      UNION
      SELECT a915~lifnr AS vendor,<br>             a915~matnr AS erpnumber,<br>             a915~datbi AS validto,<br>             a915~datab AS validfrom,<br>             konp~kbetr AS freightprice,<br>             konp~konwa AS currency,<br>             konp~loevm_ko AS deleted,<br>             a917~werks AS werks<br>             FROM a915 INNER JOIN konp ON a915~knumh = konp~knumh<br>             INNER JOIN a917 AS a917 ON a915~matnr = a917~matnr<br>                                    AND a915~kappl = a917~kappl<br>                                    AND a915~kschl = a917~kschl<br>                                    AND a915~ekorg = a917~ekorg<br>                                    AND a915~lifnr = a917~lifnr<br>                                    AND a915~datbi = a917~datbi<br>             WHERE a917~matnr IN @lt_params_matnr<br>             AND a917~lifnr IN @lt_params_lifnr<br>             INTO CORRESPONDING FIELDS OF<br>             TABLE @lt_entityset<br>             UP TO @lv_rows_read ROWS.

The problem is that UP TO .. ROWS is not allowed with unioin, therefore my paging doesn't work. I am trying to rewrite this into a cds view, hoping to bypass this problem. Here is my attempt. The problem is that lt_params_lifnr, lt_params_matnr are internal tables and I cannot pass them down to the cds view, thus getting an error. Is there a way to fix it? I am pretty new to cds views, sorry if it's not a good question. Thank you for your time.

@AbapCatalog.sqlViewName: 'ZCDS_ZMM_CDS_NAM'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'CDS view for ZCL_ZMM_ZFRZ_PRICE_DPC_EXT'

define view ZCDS_ZMM_CDS 
with parameters lc_appl         : abap.char( 2 ),
                lc_condition    : abap.char( 4 )
as select from a915 
  association [0..1] to konp as _Join1
    on _Join1.knumh = a915.knumh
{
  a915.lifnr as vendor,
  a915.matnr as erpnumber,
  a915.datbi as validto,
  a915.datab as validfrom,
  _Join1.kbetr as freightprice,
  _Join1.konwa as currency,
  _Join1.loevm_ko as deleted,
  '' as werks
}
  where a915.kappl = $parameters.lc_appl
  and a915.kschl = $parameters.lc_condition
union
select from a915
  association [0..1] to a917 as _Join1
    on _Join1.matnr = a915.matnr
    and _Join1.kappl = a915.kappl
    and _Join1.kschl = a915.kschl
    and _Join1.ekorg = a915.ekorg
    and _Join1.lifnr = a915.lifnr
    and _Join1.datbi = a915.datbi
    association [0..1] to konp as _Join2
    on _Join2.knumh = a915.knumh
{
  a915.lifnr as vendor,
  a915.matnr as erpnumber,
  a915.datbi as validto,
  a915.datab as validfrom,
  _Join2.kbetr as freightprice,
  _Join2.konwa as currency,
  _Join2.loevm_ko as deleted,
  _Join1.werks as werks
}
where a915.kappl = $parameters.lc_appl
and a915.kschl = $parameters.lc_condition
up to 5 rows.

[lenapadeken]

You could avoid the UP TO n ROWS problem using common table expressions (https://help.sap.com/doc/abapdocu_latest_index_htm/latest/en-US/index.htm?file=abapwith.htm).

WITH +cte AS ( SELECT ... UNION ... )
SELECT ... FROM +cte ...
UP TO ... ROWS.

[Sandra_Rossi]

*Formatting issue in your question (many <br>). It should be:

   SELECT a915~lifnr AS vendor,
           a915~matnr AS erpnumber,
           a915~datbi AS validto,
           a915~datab AS validfrom,
           konp~kbetr AS freightprice,
           konp~konwa AS currency,
           konp~loevm_ko AS deleted,
           ' ' AS werks
           FROM a915 INNER JOIN konp ON a915~knumh = konp~knumh
           WHERE a915~kappl = @lc_appl AND a915~kschl = @lc_condition
           AND a915~lifnr IN @lt_params_lifnr
           AND a915~matnr IN @lt_params_matnr
      UNION
      SELECT a915~lifnr AS vendor,
             a915~matnr AS erpnumber,
             a915~datbi AS validto,
             a915~datab AS validfrom,
             konp~kbetr AS freightprice,
             konp~konwa AS currency,
             konp~loevm_ko AS deleted,
             a917~werks AS werks
             FROM a915 INNER JOIN konp ON a915~knumh = konp~knumh
             INNER JOIN a917 AS a917 ON a915~matnr = a917~matnr
                                    AND a915~kappl = a917~kappl
                                    AND a915~kschl = a917~kschl
                                    AND a915~ekorg = a917~ekorg
                                    AND a915~lifnr = a917~lifnr
                                    AND a915~datbi = a917~datbi
             WHERE a917~matnr IN @lt_params_matnr
             AND a917~lifnr IN @lt_params_lifnr
             INTO CORRESPONDING FIELDS OF
             TABLE @lt_entityset
             UP TO @lv_rows_read ROWS.