Push down SELECT-OPTIONS to HANA

[former_member182046]

Hi all,

I'm optimizing a long-running ABAP report that uses some SELECT-OPTIONS/RANGES for SELECTS that are currently implemented as OpenSQL queries in the ABAP report itself.

Example:

SELECT-OPTIONS:

  s_partn FOR but000-partner.

SELECT * FROM but000 INTO TABLE lt_but000 WHERE PARTNER IN s_parn.

As I'm pushing down large parts of the ABAP logic into SQLscript Stored Procedures in HANA, I wonder what to do with the SELECT-OPTIONs. As HANA doesn't seem to support using RANGES (SIGN,OPTION, LOW, HIGH) in WHERE conditions, I'll have to

1. convert the RANGES to a HANA-compliant WHERE clause string (either in HANA or in ABAP)
2. hand it down from ABAP to HANA (either the RANGES tables or the string resulting from the conversion, depending on where I convert it)
3. execute either a fully dynamic SQL statement via EXEC or EXECUTE IMMEDIATE,
   or
4. execute a regular SELECT (which I could ideally bind to a table variable, e.g. an output parameter table) with a dynamic WHERE clause,
   or
5. use really nasty hacks such as dynamically creating Stored Procedure with a static SELECT in (either from ABAP via ADBC or within HANA) - I'd hate to have to do that.

A number of questions:

1. Is anyone aware of an existing function module or service class in ABAP or stored procedure in HANA that does the conversion from RANGES to HANA-compliant WHERE clauses? I could write one but it seems like such a classic reuse case that I hope something exists already.
2. If I use fully dynamic SQL, the problem with EXEC and EXECUTE IMMEDIATE seems to be to get hold of the result set, which I need to bind to a table variable in either the same procedure or in the calling procedure for further processing. Since result sets generated with either statement cannot be bound to table variables directly and do not even show up when calling the procedure WITH OVERVIEW, this option seems to be ruled out.
3. There doesn't seem to be the option to do a partly dynamic SELECT, where only the WHERE clause comes from a string.
4. How is this kind of requirement usually handled? I can't be the first person doing this.

Thanks a lot,

Thorsten

[former_member192616]

Hi Thorsten,

For Question 1 and on ABAP side:

I've seen this question (and possible workarounds) a few times.

Have a look at fm:

RSDS_RANGE_TO_WHERE

and

FREE_SELECTIONS_RANGE_2_WHERE

For the HANA side i'm not sure if there is a best practise yet...

Kind regards,

Hermann

[former_member182046]

Hermann,

Thanks a lot - that's already quite helpful.

RSDS_RANGE_TO_WHERE happens to have the restriction that the fieldnames must be valid as names of components of an ABAP structure, e.g. generating WHERE clauses such as

T1.PARTNER = ... AND 
T2.STREET = ...

won't work - but it's easy to use for WHERE clauses where field names do not need to be disambiguated with respect to their tables.

FREE_SELECTIONS_RANGE_2_WHERE is a bit less comfortable to use but makes up with greater flexibility.

Thanks a lot!

Best,

Thorsten

[former_member182046]

Oh my, I missed the fact that both function modules convert RANGES to WHERE clauses that are OpenSQL compliant but not Native SQL (for HANA) compliant. This is obvious when the generated WHERE clause contains operators such as 'NE'.

Does anyone happen to know similar reuse functions for HANA-compliant WHERE clauses?

Thanks,

Thorsten

[former_member192616]

Hi Thorsten,

i used it a few times with native SQL and never stumbled about that (probaly i had no "not equal" there... hm... .

Kind regards,

Hermann

[Former Member]

Hi Thorsten,

I had this post as bookmark just in case there would be more discussion on this.

This is something i could come up with to convert Select Options  into Native SQL for HANA. Probably you may have already found the solution.

SAP Demo Report      RSMDS_DEMO

Please change the MANDT and Schema Information

REPORT  zztest_hana_0001_sdn.

TABLES vbak.

PARAMETERS pa_con    TYPE dbcon_name DEFAULT 'XXX'.

SELECT-OPTIONS : so_erdat FOR vbak-erdat,

                  so_vdatu FOR vbak-erdat,

                  so_kunnr FOR vbak-kunnr.

TYPES: BEGIN OF typ_dynamic,

        vbeln TYPE vbeln,

        END OF typ_dynamic.

*-- HANA Call Variables

DATA l_sql_con   TYPE REF TO cl_sql_connection.

DATA l_sql       TYPE REF TO cl_sql_statement.

DATA l_result    TYPE REF TO cl_sql_result_set.

DATA l_statement TYPE string.

DATA ls_dbcon    TYPE dbcon.

DATA lt_result   TYPE STANDARD TABLE OF typ_dynamic.

DATA ls_result   TYPE typ_dynamic.

DATA lf_result   TYPE REF TO data.

DATA l_string    TYPE string.

*-- Runtime Calc Variables

DATA dbcnt       TYPE sy-dbcnt.

DATA seconds     TYPE i.

DATA rate        TYPE i.

DATA time_start  TYPE timestampl.

DATA time_stop   TYPE timestampl.

*-- Where clause generation

DATA acc_gen_cp TYPE rsmds_boolean.

DATA r_universe TYPE REF TO cl_rsmds_universe.

DATA r_dim0     TYPE REF TO if_rsmds_dimension.

DATA r_dim1     TYPE REF TO if_rsmds_dimension.

DATA r_renderer TYPE REF TO if_rsmds_condition_renderer.

DATA r_dim2     TYPE REF TO if_rsmds_dimension.

DATA r_dim3     TYPE REF TO if_rsmds_dimension.

DATA r_seti     TYPE REF TO cl_rsmds_set.

DATA r_set      TYPE REF TO cl_rsmds_set.

DATA incl_null  TYPE rsmds_boolean.

DATA r_set0     TYPE REF TO cl_rsmds_set.

DATA rx_message TYPE REF TO cx_rsmds_message.

DEFINE start_timer.

   get time stamp field time_start.

END-OF-DEFINITION.

DEFINE stop_timer.

   get time stamp field time_stop.

   perform calculate_rate

     using    time_start time_stop dbcnt

     changing &2.

   check &1 = &1.

END-OF-DEFINITION.

*&---------------------------------------------------------------------*

*&      Form  calculate_rate

*&---------------------------------------------------------------------*

FORM calculate_rate

   USING

     i_time_start LIKE time_start

     i_time_stop  LIKE time_stop

     i_records    TYPE i

   CHANGING

     e_rate       LIKE rate.

   DATA:

     l_date_start TYPE d,

     l_date_stop  TYPE d,

     l_time_start TYPE t,

     l_time_stop  TYPE t.

   CONSTANTS:

     c_tzone_utc TYPE timezone VALUE IS INITIAL.

   IF i_time_start LT i_time_stop.

     CONVERT TIME STAMP i_time_start TIME ZONE c_tzone_utc

             INTO DATE l_date_start TIME l_time_start.

     CONVERT TIME STAMP i_time_stop TIME ZONE c_tzone_utc

             INTO DATE l_date_stop  TIME l_time_stop.

     TRY.

         e_rate = i_records /

                  ( ( l_date_stop  * 86400 + l_time_stop  + frac( i_time_stop  ) ) -

                    ( l_date_start * 86400 + l_time_start + frac( i_time_start ) )   ).

       CATCH cx_sy_arithmetic_overflow.

         CLEAR e_rate.

     ENDTRY.

   ELSE.

     CLEAR e_rate.

   ENDIF.

ENDFORM.                    "calculate_rate

START-OF-SELECTION.

   start_timer.

   r_universe = cl_rsmds_ddic_universe=>create_by_tabname( 'VBAK' ).

* Get references of dimensions from the dimension model by name

   r_dim0 = r_universe->get_dimension_by_name( 'MANDT' ).

   r_dim1 = r_universe->get_dimension_by_name( 'KUNNR' ).

   r_dim2 = r_universe->get_dimension_by_name( 'ERDAT' ).

   r_dim3 = r_universe->get_dimension_by_name( 'VDATU' ).

   r_renderer = cl_rsmds_native_sql_renderer=>get_condition_renderer( ).

   r_set0     = r_dim0->create_set_from_value( sy-mandt ).

   r_seti     = cl_rsmds_set=>get_universal_set( ).

   TRY.

       r_set = r_dim1->create_set_from_ranges(

                 i_t_ranges                = so_kunnr[]

                 i_final                   = rsmds_c_boolean-true

                 i_accept_general_patterns = acc_gen_cp           ).

     CATCH cx_rsmds_input_invalid INTO rx_message.

       MESSAGE rx_message TYPE 'I'.

       LEAVE PROGRAM.

   ENDTRY.

   r_seti = r_seti->intersect( r_set ).

   TRY.

       r_set = r_dim2->create_set_from_ranges(

                 i_t_ranges                = so_erdat[]

                 i_final                   = rsmds_c_boolean-true

                 i_accept_general_patterns = acc_gen_cp           ).

     CATCH cx_rsmds_input_invalid INTO rx_message.

       MESSAGE rx_message TYPE 'I'.

       LEAVE PROGRAM.

   ENDTRY.

   r_seti = r_seti->intersect( r_set ).

   TRY.

       r_set = r_dim3->create_set_from_ranges(

                 i_t_ranges                = so_vdatu[]

                 i_final                   = rsmds_c_boolean-true

                 i_accept_general_patterns = acc_gen_cp           ).

     CATCH cx_rsmds_input_invalid INTO rx_message.

       MESSAGE rx_message TYPE 'I'.

       LEAVE PROGRAM.

   ENDTRY.

   r_seti = r_seti->intersect( r_set ).

   l_string = r_seti->to_string(

                i_r_renderer = r_renderer

                i_include_null_conditions = incl_null  ).

   GET REFERENCE OF lt_result INTO lf_result.

   SELECT SINGLE * FROM dbcon INTO ls_dbcon

    WHERE con_name = pa_con

      AND dbms     = 'HDB'.  "HANA

   IF sy-subrc NE 0.

     MESSAGE 'Invalid DB Con ID' TYPE 'E'.

   ENDIF.

   l_sql_con = cl_sql_connection=>get_connection( pa_con ).

   CREATE OBJECT l_sql

     EXPORTING

       con_ref = l_sql_con.

   l_sql->execute_ddl( |set schema { 'XXX'  }| ).    "Please substitue your Schema Name

   IF l_string IS NOT INITIAL.

     l_statement = |select vbeln from "VBAK" where mandt = '800' and |.

     CONCATENATE l_statement l_string INTO l_statement.

   ELSE.

     l_statement = |select vbeln from "VBAK" where mandt = '800' |.

   ENDIF.

   TRY.

       l_result = l_sql->execute_query( l_statement ).

       l_result->set_param_table( lf_result ).

       l_result->next_package( ).

       l_result->close( ).

     CATCH cx_sql_exception.

       MESSAGE 'Call to HANA System Failed' TYPE 'E'.

   ENDTRY.

   dbcnt = lines( lt_result ).

   stop_timer dbcnt rate.

   WRITE: / 'ABAP (HANA DB)  :   SY-DBCNT =', dbcnt, 'RATE =', rate, '1/S'.

Regards,

Arun Sambargi

[Gairik]

Hi Thorsten

It is a old post ( 2 months ) and I think you have already found the solution. If not , then you can check the below solution .

There's a new class CL_LIB_SELTAB in the package SHDB_TOOLS

We have to use this private class in our program to . Below, I'm giving a simple example

As the class is a private class , call the method "NEW" to get its instance . pass the selection-options in the parameter it_sel. Now, in the next step you can call the method SQL_WHERE_CONDITION passing the actual field name . here you can use alias name or anything which will be treated as a field name in hana. I have passed 'A.MATNR' assuming that A is the alias name of the HDB table . While executing I have passed 2 parameters , one with NE option and another with GT option. Please see the screenshot how the values are getting returned. Please check and let us know whether this works for you

------------------------------------------------------------------------------------------------------------------------

REPORT ZTEST.
TABLES: MARA .
SELECT-OPTIONS S_MATNR FOR MARA-MATNR.

DATA: O_COND TYPE REF TO CL_LIB_SELTAB,
      H_HANDLE TYPE REF TO CL_ABAP_TABLEDESCR,
      L_COND TYPE STRING.


CALL METHOD CL_LIB_SELTAB=>NEW
   EXPORTING
     IT_SEL = S_MATNR[]
   RECEIVING
     RR_REF = O_COND
     .

CALL METHOD O_COND->SQL_WHERE_CONDITION
   EXPORTING
     IV_FIELD = 'A.MATNR'
   RECEIVING
     RV_COND  = L_COND
     .


BREAK-POINT.

------------------------------------------------------------------------------------------------------------------------------------------

[former_member213277]

data: vint type int.

SELECT-OPTIONS: S_NAME FOR VINT.

DATA: VSTRING TYPE STRING.

DATA: VCOUNT TYPE I

LOOP AT S_NAME .

         CONCATENATE S_NAME-LOW  VSTRING INTO VSTRING SEPARATED BY ','

ENDLOOP.

VCOUNT   = STRLEN( VSTRING )

VCOUNT = VCOUNT - 1.

VSTRING = VSTRING(VCOUNT).        " Remove the comma in the last position of strng and Now vstring contains all the values.

" Prepare native SQL

LV_SQL = |SELECT * FROM <SCHEMA_NAME>.<HANA_VIEW> WHERE NAME IN (| && VSTRING && |)|.

CREATE OBJECT LO_SQL.

LO_RESULT = LO_SQL->EXECUTE_QUERY( LV_SQL ).

GET REFERENCE OF IT_HEADER INTO LR_DATA.

LO_RESULT->SET_PARAM_TABLE( LR_DATA ).

LO_RESULT->NEXT_PACKAGE( ).

LO_RESULT->CLOSE( ).

Please not that above logic will work only for single multiple entries i.e. SIGN = 'I' and OPTION = 'EQ'.

Thanks and Regards,

Nagaraj

[former_member213277]

Hi,

We also had the same question but when checked with SAP, they mentioned that-- There is no difference in the Performance  fetching data from Dictionary View and Fetching data from HANA View via ADBC.. because the open sql is converted into native sql and hence filtering will happen at DB level.

    So we can use DDIC view and use the SELECT-OPTION Values in the Where Clause of the Open SQL

I read in some blogs that in SP2 there are some API's are available using which we can pass the SELECT-OPTION values to HANA View(e.g. Calculation VIew) via DDIC View.

You can use the FM: RSDS_RANGE_TO_WHERE to convert select Option values into where clause

Thanks and Regards,

Nagaraj