Intersect of two tables fails in SAP ABAP CDS

Question

I want to check if two columns of two tables are indeed related. For which I am using a simple INTERSECT between two columns of different tables that I know are related. If a match in data is found, that would mean data from one table is present in the other.

select customid from sbook
INTERSECT
select id from scustom 

However, this results in an error> Query Execution failed UP TO n ROWS currently cannot be used in INTERSECT

How to get this going?

Answer 1

ABAP open SQL statement UP TO n ROWS gets converted to LIMIT n in native SQL.

Please note that DDL SQL of CDS views has limitations and it doesnt support neither 'UP TO n ROWS' nor TOP/LIMIT statements.

Option 1: Is to implement CDS table function which uses HANA Native SQL and has support for TOP/LIMIT statements. Sample code is as below.

define table function zmsk_intersect_tabfunc 
returns {
   mandt: abap.clnt;
   customid   : s_customer;  
}
implemented by method zmsk_intersect_tabfnc=>get_data;

CLASS zmsk_intersect_tabfnc DEFINITION
  PUBLIC FINAL CREATE PUBLIC .

  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb .
    CLASS-METHODS get_data.

ENDCLASS.

CLASS zmsk_intersect_tabfnc IMPLEMENTATION.
  METHOD get_data BY DATABASE PROCEDURE FOR HDB LANGUAGE
  SQLSCRIPT USING sbook scustom.

*    SELECT TOP 10 * FROM (
    SELECT customid  AS cust FROM sbook
    INTERSECT
    SELECT id AS cust FROM scustom
    LIMIT 10 --Comment this line when TOP is used
*    )
     ;
  ENDMETHOD.
ENDCLASS.

Option 2: If the CDS view is accessed from ABAP code, you can add UP TO n ROWS to the select statement in ABAP.