Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

How to use variable in PLACEHOLDER in a SQL query ABAP progamming.

former_member290259
Discoverer
0 Kudos

In ABAP programming I am using one query which fetches data from table. The same query is being used to show result in HANA Studio. Just some syntactical changes. But query contains two placeholder with its value. In abap programming I am trying to convert those placeholder values to parameter. e.g. the query I am using in ABAP programming is as below:

'Select * FROM "_SYS_BIC"."sap.is.retail.car/InventoryVisibilityWithSalesOrderReservedQuantity" (' & '''' & 'PLACEHOLDER' & '''' & '= (' & '''' & '$$P_DateFrom$$' & ''''  & ',' & '''' & '20160510' & '''' & '),' & '''' & 'PLACEHOLDER' & '''' & '= (' & '''' & '$$P_DateTo$$' & '''' & ',' & '''' & '20160510'

  & '''' & '))'.

This query is giving me proper result. But I want to use parameter in place of date value (Highlighted in RED). I am not sure about the syntax to use varibles in this kind of query. Please let me know if anyone is having any info.

WR,

Sumanjit

7 REPLIES 7

kilian_kilger
Active Participant
0 Kudos

Hi Sumanjit,

do you use ADBC to do the query? Then you have to write a questionmark "?" in the query string and bind the input parameters via CL_SQL_STATEMENT=>SET_PARAM. If you use the (deprecated) EXEC SQL, you can use the ":variable" syntax. Please see the ABAP help.

Best regards,
Kilian.

0 Kudos

Hi Kilian

I am not aware of how to bind the input parameters via CL_SQL_Statement. A little more explanation would be appreciated.

WR,

Sumanjit

0 Kudos

Hi Sumanjit,

the following code gives all entries with SPRSL = 'E' from T100:


DATA lt_table TYPE STANDARD TABLE OF t100 WITH EMPTY KEY.

DATA(statement) = NEW cl_sql_statement( ).

statement->set_param( EXPORTING data_ref REF #( 'E' ) ).

DATA(result_set) = statement->execute_query( |SELECT * FROM T100 WHERE SPRSL = ? LIMIT 5| ).

result_set->set_param_table( itab_ref = REF #( lt_table ) ).

result_set->next_package( ).

result_set->close( ).

cl_demo_output=>display( lt_table ).




Best regards,

Kilian.

0 Kudos

I am using the following query. Not sure how to migrate your solution. i just want to change the place holder values with variable. Do i need to change the query format completely. The ans that you provided looks a bit different than mine.

'Select * FROM "_SYS_BIC"."sap.is.retail.car/InventoryVisibilityWithSalesOrderReservedQuantity" (' & '''' & 'PLACEHOLDER' & '''' & '= (' & '''' & '$$P_DateFrom$$' & ''''  & ',' & '''' & '20160510' & '''' & '),' & '''' & 'PLACEHOLDER' & '''' & '= (' & '''' & '$$P_DateTo$$' & '''' & ',' & '''' & '20160510'

  & '''' & '))'.

Sandra_Rossi
Active Contributor
0 Kudos

It seems your syntax is not classic ABAP, because it would not compile. Are you sure this is ABAP? Could you paste a few lines from your "ABAP" source code before and after your SELECT statement?

Message was edited by: Sandra Rossi Update: I think I understood what you did: It could compile (surrounded by something), but & is not an operator but a continuation character of text literal, and it's limited to 255 characters. Moreover you can't insert variables. Instead, you should use the && operator (>=7.02).

0 Kudos

Hi Rossi

I think you got my question clearly. let me help you more. I am pasting some of code lines. Let me know whether we can use variable in place of dates.

lr_dbconn = cl_sql_connection=>get_connection( con_name = lv_con ).

  create object lr_sql_env

    exporting

      con_ref = lr_dbconn.

*Define sql script

  lv_sql = 'Select * '.

  lv_from = 'FROM "_SYS_BIC"."sap.is.retail.car/InventoryVisibility'.

  lv_from1 = 'WithSalesOrderReservedQuantity" (' & '''' & 'PLACEHOLDER' & '''' & '= (' & '''' & '$$P_DateFrom$$' & ''''  & ',' &

  '''' & '20160510' & '''' & '),' & '''' & 'PLACEHOLDER' & '''' & '= (' & '''' & '$$P_DateTo$$' & '''' & ',' & '''' & '20160510'

  & '''' & '))'.

  concatenate lv_from lv_from1 into lv_from respecting blanks.

  lv_where1 = 'WHERE "SAPClient" = ''' && V_CLIENT && ''''.

  lv_where2 = 'AND "Location" = ''' && V_PLANT && ''''.

  lv_where3 = 'AND "Article" = ''' && V_ARTICLE && ''''.

     I am consolidating all the lv_* variables. & executing the query to run in HANA Studio.

Thanks

Sumanjit

0 Kudos

The solution to your issue is what I said in my first answer, in the "update" part, and I complement a little bit:

I think I understood what you did: It could compile (surrounded by something), but & is not an operator (although ambiguously named "literal operator") but a continuation character for linking several text literals (it was useful at the time when ABAP source code lines were limited to 72 characters), and it's limited to link up to 255 characters. Moreover you can't insert variables or constants. Instead, you should use the && operator (>=7.02).

So replace & with &&, and use a variable containing the date (DATA datefrom TYPE d VALUE '20160510', and you integrate it using '$$P_DateFrom$$' && '''' && ',' && '''' && datefrom && '''' etc.

As I said previously, '$$P_DateFrom$$' & ''''  & ',' & '''' & datefrom & '''' (with only &) is an erroneous syntax (cannot compile) because datefrom is not a text literal.