AMDP Class accessing virtual table - need to implement Dynamic SQL

[ABAPMarty]

Hi, I have a problem with accessing virtual tables via a table function and AMDP class. I am an ABAP Developer so I am struggling with the SQLScript syntax and a few other things too so I am hoping to get some help.

We have a remote data source, create with SDA (smart data access). We have 200 plants. Each plant has an SQL DB that we are extracting data from into a SQL Server staging server. The data for each plant has a table named after that plant number. So say the table names are: 1000, 1001, 1002, etc. Every table has exactly the same fields.

I have created virtual tables for these tables that exist on the remote data source. I also ran a "grant select" statement to give access to those tables. So I have a working example with a hard coded plant number. Now I want to pass the plant to the table function as a parameter and use that to build the text of the table name to use dynamically in the "FROM" clause. The table name would be something like: "TAB_[plantno]". As I am not fluent in SQLScript I have done a lot of googling, read some of the "SAP HANA SQLScript Reference" document and tried various things.

For instance on the staging table we created one view which was a union of all the others. Then we don't need dynamic SQL, we can just specify the plant in the "WHERE" clause as it is the first field in each table. But it times out when we run it. Just too much data to be able to get a useful response.

So these objects give a result, with 10 records from the virtual table:

Table function:

define table function ZTF_MC_TEST_DYNAMIC_SQL
    with parameters p_plant : werks_d
    returns 
    {
    key PLANT   : abap.char( 4 ) ;
    key MATERIAL : abap.char( 18 ); 
    } implemented by method zcl_mc_test_dyn_sql_sda=>get_data;

AMDP Class:

CLASS zcl_mc_test_dyn_sql_sda DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .


  PUBLIC SECTION.
    INTERFACES: if_amdp_marker_hdb .
    CLASS-METHODS get_data FOR TABLE FUNCTION ztf_mc_test_dynamic_sql .


  PRIVATE SECTION.
    TYPES: BEGIN OF select_typ ,
             plant   TYPE c LENGTH 4,
             article TYPE c LENGTH 18,
           END OF select_typ .
    DATA: lt_select TYPE STANDARD TABLE OF select_typ .
    CLASS-METHODS build_sql_statement IMPORTING iv_plant      TYPE werks_d
                                      RETURNING VALUE(rv_sql) TYPE string .


ENDCLASS.


CLASS zcl_mc_test_dyn_sql_sda IMPLEMENTATION.
  METHOD: get_data BY DATABASE FUNCTION FOR HDB
                    LANGUAGE SQLSCRIPT
                    OPTIONS READ-ONLY .
    RETURN SELECT top 10 "Plant" as PLANT, "MaterialNo" as MATERIAL FROM "<SCHEMA_NAME>"."TAB_1001" ;
  ENDMETHOD.
ENDCLASS.

======================================================================================

This is what I want to do but I can't get it working: I don't think I need to make changes to the table function, although note that it has p_plant as an importing parameter.

    CLASS-METHODS build_sql_statement IMPORTING iv_plant      TYPE werks_d
                                      RETURNING VALUE(rv_sql) TYPE string .


  METHOD build_sql_statement .
    rv_sql = `"<SCHEMA_NAME>"."TAB_` && |{ iv_plant }| && `"` .
  ENDMETHOD.

  METHOD: get_data BY DATABASE FUNCTION FOR HDB
                    LANGUAGE SQLSCRIPT
                    OPTIONS READ-ONLY .
*   create the from statement with a method call (note the use of the table function parameter)
    DECLARE lv_from nvarchar(30) = call "ZCL_MC_TEST_DYN_SQL_SDA=>BUILD_SQL_STATEMENT" ( iv_plant => 
     :p_plant ) ;
*   Do the dynamic SQL call using the dynamically created "from" statement
    EXECUTE IMMEDIATE 'RETURN SELECT top 10 "Plant" as PLANT, "Plu_SapArticleNo" as ARTICLE 
       FROM' || lv_from || ;

  ENDMETHOD.

This does not work but demonstrates what I am trying to achieve, any suggestions?

[ABAPMarty]

If I hard code the value but use it dynamically, as below (for testing purposes, I get an error:

SQLSCRIPT message: general error: RETURN statement should be defined for table function &A0&A1&A2&A3&A4&A5&A6&A7&A8&A9

  METHOD: get_data BY DATABASE FUNCTION FOR HDB
                    LANGUAGE SQLSCRIPT
                    OPTIONS READ-ONLY .
    declare lv_dynsql nvarchar(30) = '"<SCHEMA_NAME"."1001"' ;
    execute immediate 'RETURN SELECT top 1000 "Plant" as PLANT, "Plu_SapArticleNo" as ARTICLE FROM '' || lv_dynsql || ''' ; 
  ENDMETHOD.<br>

[ABAPMarty]

If I try and do the concatenation of the schema and plant in one step, I get this error:

SQLSCRIPT message: general error: RETURN statement should be defined for table function &A0&A1&A2&A3&A4&A5&A6&A7&A8&A9

  METHOD: get_data BY DATABASE FUNCTION FOR HDB
                    LANGUAGE SQLSCRIPT
                    OPTIONS READ-ONLY .
    declare lv_dynsql nvarchar(30) = '"<SCHEMA_NAME>"."''|| :p_plant || "''' ;
    execute immediate 'RETURN SELECT top 1000 "Plant" as PLANT, "Plu_SapArticleNo" as ARTICLE FROM '' || lv_dynsql || ''' ; 
  ENDMETHOD.

[ABAPMarty]

I have this working in the SQL Console. I don't think you can use RETURN SELECT with EXECUTE IMMEDIATE. So I think this has to selected "INTO" a table and then on the next line "RETURN SELECT" from that internal table.

do
begin
declare lv_plant nvarchar(4) = '1001';
declare lv_dynsql nvarchar(30) = '"<SCHEMA_NAME>"."' || :lv_plant || '"' ;
execute immediate 'SELECT top 1000 "Plant" as PLANT, "Plu_SapArticleNo" as ARTICLE FROM '|| lv_dynsql ||'' ; 
end;

[ABAPMarty · Accepted solution]

Wow, after much experimenting and A LOT of help from a very smart colleague I got this to work.

There are some learning notes here:

• It seems you cannot have a "RETURN SELECT" statement with "EXECUTE IMMEDIATE"
• You have to use "EXEC" or "EXECUTE IMMEDIATE" with a dynamic SQL statement
• So you select into a temporary table
• Without "RETURN SELECT" in the "EXECUTE IMMEDIATE" statement, you use "RETURN SELECT" on the next line to populate the result iterator of the table function.

  METHOD: get_data BY DATABASE FUNCTION FOR HDB
                    LANGUAGE SQLSCRIPT
                    OPTIONS READ-ONLY .
*   Results table
    declare lt_out table (plant nvarchar(4),
                          article nvarchar(18));
*   Dynamic FROM statement
    declare lv_dynfrom nvarchar(30) = '"<SCHEMA_NAME>"."' || :p_plant || '"' ;
*   Execute dynamic SQL statement
    execute immediate 'SELECT top 1000 "Plant" as PLANT, "Plu_SapArticleNo" as ARTICLE FROM '|| lv_dynfrom ||'' into lt_out ;
*   Put select result into result iterator
    RETURN SELECT * FROM :lt_out;
  ENDMETHOD.

Will mark this as done.