05-24-2016 7:12 AM
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
05-24-2016 11:38 AM
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.
05-24-2016 12:35 PM
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
05-24-2016 2:22 PM
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.
05-26-2016 11:17 AM
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'
& '''' & '))'.
05-26-2016 9:52 PM
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).
05-27-2016 5:23 AM
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
05-27-2016 8:18 AM
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.