ABAP 7.5 - Extended Result in SELECT error

[Former Member]

I have created 2 CDS Views as below:

@AbapCatalog.sqlViewName: 'ZV_CDS_INVC'
@EndUserText.label: 'Open Sales Order invoices'
define view ZCDSV_OPEN_INVOICE
  as select from snwd_so_inv_head
{
  key snwd_so_inv_head.buyer_guid,
      'C' as category
}
where
  snwd_so_inv_head.payment_status <> 'P'
group by
  snwd_so_inv_head.buyer_guid
having
      count(
        distinct snwd_so_inv_head.node_key
      ) <= 2000


union all


select from snwd_so_inv_head
{
  key snwd_so_inv_head.buyer_guid,
      'D' as category
}
where
  snwd_so_inv_head.payment_status <> 'P'
group by
  snwd_so_inv_head.buyer_guid
having
      count(
        distinct snwd_so_inv_head.node_key
      ) >  2000
  and count(
    distinct snwd_so_inv_head.node_key
  )     <= 4000


union all


select from snwd_so_inv_head
{
  key snwd_so_inv_head.buyer_guid,
      'S' as category
}
where
  snwd_so_inv_head.payment_status <> 'P'
group by
  snwd_so_inv_head.buyer_guid
having
      count(
        distinct snwd_so_inv_head.node_key
      ) >  4000 
 

@AbapCatalog.sqlViewName: 'ZV_CDS_CUST'
@EndUserText.label: 'Customer Classification'
define view zcdsv_cust_classification
  as select from snwd_bpa           as bpa
    join         ZCDSV_OPEN_INVOICE as opn_inv on bpa.node_key = opn_inv.buyer_guid
{
  key bpa.bp_id        as customer_id,
      bpa.company_name as customer_name,
      opn_inv.category
} 

For the below report, when I am using extended result option in select eclipse is throwing an error as "Used the associated entity ". When I put the HANA SQL View name instead of SE11 View name, it works. Why so??

REPORT ztest_read_select.


DATA(mv_hints) = '&SUPPORTS_FDA_PROT 0&'.
DATA(lv_limit) = 0.
DATA lv_group_by TYPE string.
DATA lv_order_by TYPE string.
DATA(lv_dbcon) = 'DEFAULT'.
DATA(locale) = '%_native( ''WITH PARAMETERS( ''LOCALE'' = ''EN'' )'' )'.
DATA(o_ref) = NEW cl_osql_extended_result( iv_cached_view = 'X' ).


SELECT  customerclassificatio_0~customer_id AS customer_id,
        customerclassificatio_0~customer_name AS customer_name,
        customerclassificatio_0~category AS category
        FROM zv_cds_cust AS customerclassificatio_0
        GROUP BY (lv_group_by)
        ORDER BY (lv_order_by)
        %_HINTS HDB @mv_hints
        INTO TABLE @DATA(et_data)
        EXTENDED RESULT @o_ref
        UP TO @lv_limit ROWS
        CONNECTION (lv_dbcon).


cl_demo_output=>display_data( et_data ).

[horst_keller]

"However, the select statement that I added is from the class CL_SADL_SQL_STATEMENT (open-sql.jpg) which is internally called during ODATA method calling if you have consumed CDS entities directly from gateway."

Now I understand where the '%_native( ''WITH PARAMETERS( ''LOCALE'' = ''EN'' )'' )' in your code comes from. Because this addition is not released for customers but for SADL internal use only. Same for the usage of EXTENDED RESULT SET in that case.

-> The problem arises from the usage of specialized Open SQL in the SADL framework and cannot be answered from pure ABAP. It must be addressed to the respective component. I will notify someone I know, but cannot promise anything.

[Former Member]

@Horst: Have you any updates about the same?

[horst_keller]

No, nothing. If you think it is an error, feel free to open a ticket.

[Former Member]

Thanks a lot Horst & Sagar for the quick response!

However, the select statement that I added is from the class CL_SADL_SQL_STATEMENT (open-sql.jpg) which is internally called during ODATA method calling if you have consumed CDS entities directly from gateway.

I tried to debug it and in course found the below observation:

If I remove the joins in between CDS views, then the same ODATA method is working. But, if I go for the above mentioned CDS joins and tried to consume ODATA, then the JSON of the EntitySet returns the error : "Used the associated entity"

But if I put the HANA View name instead, it works 🙂

I researched a lot on the internet and found nothing on the same and ODATA service is just not working with the joined CDS entities.

[Former Member]

Hi Amalendu,

I suppose, you meant to say that you get the said error when using the sql view (specified using the annotation - @AbapCatalog.sqlViewName) as the data source as opposed to the CDS entity.

As of now, the EXTENDED RESULT addition is meant to be used only with cached views (existing in the HANA DB). To simplify it further, the view created in the HANA DB should be created using the addition - WITH CACHE .

There is no provision currently to specify caching support for CDS data definition or the corresponding DB view from the application server except for adding the caching support via ADBC. Please refer to the report - demo_select_cached_view and the corresponding documentation to get more clarification on this.

Also, the HINT - RESULT_CACHE needs to be specified in the open SQL query while using EXTENDED RESULT addition in order to activate reading from the cache and to get a valid result set assigned to the CL_OSQL_EXTENDED_RESULT object.

But I'm not sure why only CDS entities are allowed as data source and not data dictionary views when using EXTENDED RESULT, there is no clear documentation pointing out the reason for the same.

May be because CDS data definitions are just design time artifiacts and not actual DB objects and thus the caching support cannot be checked on them directly, just a wild guess.

Thanks,
Sagar J.