1

can I place a sql query in a sql hana place holder?

the following query works:

     Select * From table1
     ('PLACEHOLDER' = ('$$IP_ShipmentDate$$', '2020-01-01'))

I am trying to pass something like the following:

      Select * From table1
     ('PLACEHOLDER' = ('$$IP_ShipmentDate$$', 
       Select TO_VARCHAR(min("some_date"),'YYYY-MM-DD') from Table2)

Currently getting a syntex error

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Manny
  • 87
  • 2
  • 14

2 Answers2

1

No, that's not supported.

The parameter interface is for character strings only. SELECTs or expressions won't be evaluated by HANA.

To achieve dynamic selection parameters (say, the current date or three weeks ago) you may want to look into setting the default values via stored procedures or into computing the desired values in the client (front-end) tool.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29
  • 1
    It is not always true. If you deal with already existing parameter, then it is. If you design a new calc view, then there's a hack: if you remove quotes around parameter (that are placed around it if you add it via double-click in expression editor) it will be evaluated as expression. Tested in 2.0sp04. I've found this hack while looked for a way to create a common filter for group of calculation views. – astentx Dec 21 '20 at 00:10
  • 1
    You may want to be careful with using undocumented behavior; there’s explicitly no support for it and it may disappear as quickly as it appeared in the HANA software. If you don’t have a document from SAP saying this should work I highly recommend disregarding this for anything but testing/fiddling around. – Lars Br. Dec 21 '20 at 06:23
1

As error says you, expressions are not supported for column view parameter: Can't use column expression as column view parameter.

However, there are options to calculate it:

  • If your parameter's value is semantically related to this calculated view (e.g. you need some default value for this view that is calculated based on some logic), then you can create a stored procedure with one output parameter to calculate the value, and use parameter type Derived From Procedure/Scalar Function. To make that value adjustable from frontend you can mark it as Input enabled. Or if calculated value is based on user input, you can map input parameters of procedure to input parameters of calculation view in section Parameters/Variables -> Procedures/Scalar functions for input parameters.
create procedure sp_dummy (
  in dummy nvarchar(10) default '0',
  out val nvarchar(10)
)
as begin
  val = to_char(current_date, 'yyyymmdd');
end;

13

  • If your parameter's value is not related to calculation view and you just use calculation view to get some data for that value, you can use the result set feature of stored procedure/SQLScript block. If your SQL executor does not attempt to parse SQL statement (for example, SAP BO checks for SELECT as the first SQL word after some SP onwards, so this way will not be valid for it) and just gets result set as metadata for column definitions, then you can avoid extra object to wrap calculation logic.

To return a result set, use a SELECT statement because anonymous blocks do not have any parameters defined.

do begin
  declare lv_param nvarchar(100);
  select max('some_date')
    into lv_param
  from dummy /*your_table*/;
  
  select *
  from "_SYS_BIC"."path.to.your.view/CV_TEST" (
    PLACEHOLDER."$$P_DUMMY$$" => :lv_param
  );
end;

That with calculation view based on the table RSPCLOGCHAIN filtered by output of the procedure declared above gives me

LOG_ID                    | DATUM    | INPUT_PARAM | CUR_DATE
--------------------------+----------+-------------+----------
8FRV39X1O8814X6IJMLPI47PV | 20201221 | some_date   | 20201221
C7S9EY3J0GYA76Y2S9CIA39QR | 20201221 | some_date   | 20201221
35R8A3RFUG00EBY8MZQWJLIXF | 20201221 | some_date   | 20201221
850MVHOJIKIDD7EVY6WFIYPZN | 20201221 | some_date   | 20201221
astentx
  • 6,393
  • 2
  • 16
  • 25
  • I am trying to enter as following: `From "table1" ('PLACEHOLDER' = ('$$IP_ShipmentStartDate$$',lv_param), 'PLACEHOLDER' = ('$$IP_ShipmentEndDate$$', '2021-01-01'))` '' still getting a syntax error (Note that the parameter works ok, when adding lv_param as a new column it shows the correct value – Manny Dec 23 '20 at 06:04
  • 1
    @Manny Yes, in SQL Script you need to pass the parameter via arrow notation `=>`. And use colon in front of variable: `From "table1" ( PLACEHOLDER."$$IP_ShipmentStartDate$$" => :lv_param, PLACEHOLDER."$$IP_ShipmentEndDate$$" => '2021-01-01' )`. – astentx Dec 23 '20 at 07:02
  • @astenx trying to get it to work with a placeholder to pass a star (*) but getting error, is there a syntax for the star too? `FROM "table1" ( PLACEHOLDER."$$IP_ShipmentStartDate$$" => :lv_param, PLACEHOLDER.'$$IP_ItemTypecd$$' => '''*''', PLACEHOLDER."$$IP_ShipmentEndDate$$" => '2030-01-01' )` – Manny Dec 23 '20 at 12:31
  • @Manny it should not be quoted, as it is a plain text string. You should check the trace file and get the error text, it will mark wrong syntax like `error in ... expression... : int(if(int(1=0),int(2), [here]string('A')))`. That `[here]` is the place with error. – astentx Dec 23 '20 at 15:23
  • the error im getting is: `Could not execute 'do begin declare start_date nvarchar(100); select TO_VARCHAR(min("DateSQL"),'YYYYMMDD') into ...' SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "$$IP_ItemTypecd$$": line 72 col 16 (at pos 2725)` line 72 is the line with the star placeholder – Manny Dec 23 '20 at 15:50