CODEWITHSUNDEEP
sqljdbchana
Manny
Manny

Reputation: 87

sap hana placeholders pass * parameter with arrow notation

Trying to pass a star (*) in a sql Hana place holder with an arrow notation

The following works OK:

 Select *  FROM "table_1"
            ( PLACEHOLDER."$$IP_ShipmentStartDate$$" => '2020-01-01',
              PLACEHOLDER."$$IP_ShipmentEndDate$$" => '2030-01-01' )

In the following, when trying to pass a *, i get a syntax error:

   Select *  FROM "table1"
            ( PLACEHOLDER."$$IP_ShipmentStartDate$$" => '2020-01-01',
              PLACEHOLDER.'$$IP_ItemTypecd$$' => '''*''',
              PLACEHOLDER."$$IP_ShipmentEndDate$$" => '2030-01-01' )

The reason i am using the arrow notation, is since its the only way i know that allows passing parameters as in the example bellow: (as in linked post)

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;

Upvotes: 0

Views: 5142

Answers (2)

astentx
astentx

Reputation: 6751

There's a typo in your code. You need to use double quotes around parameter name, but you have a single quote. It should be: PLACEHOLDER."$$IP_ItemTypecd$$".

When you pass something to Calculation View's parameter, you already have a string, that will be treated as string and have quotes around it where they needed, so no need to add more. But if you really need to pass some quotes inside the placeholder's value you also need to escape them with backslash complementary to doubling them (it was found by doing data preview on calculation view and entering '*' as a value of input parameter, then you'll find valid SQL statement in the log of preview):

do
begin
  select *
  from "_SYS_BIC"."ztest/CV_TEST_PERF"(
    PLACEHOLDER."$$P_DUMMY$$" => '''*'''
  );
end;

/*
SAP DBTech JDBC: [339]: invalid number: : line 3 col 3 (at pos 13): invalid number:
not a valid number string '' at function __typecast__() 
*/

/*And in trace there's no more information, but interesting part
is preparation step, not an execution

w SQLScriptExecuto se_eapi_proxy.cc(00145) : Error <exception 71000339:
not a valid number string '' at function __typecast__()
> in preparation of internal statement:
*/

do
begin
  select *
  from "_SYS_BIC"."ztest/CV_TEST_PERF"(
    PLACEHOLDER."$$P_DUMMY$$" => '\'*\''
  );
end;

/*
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "\": line 5 col 38 (at pos 121)
*/

But this is ok:

do
begin
  select *
  from "_SYS_BIC"."ztest/CV_TEST_PERF"(
    PLACEHOLDER."$$P_DUMMY$$" => '\''*\'''
  );
end;

LOG_ID                    | DATUM    | INPUT_PARAM | CUR_DATE
--------------------------+----------+-------------+---------
8IPYSJ23JLVZATTQYYBUYMZ9V | 20201224 | '*'         | 20201224
3APKAAC9OGGM2T78TO3WUUBYR | 20201224 | '*'         | 20201224
F0QVK7BVUU5IQJRI2Q9QLY0WJ | 20201224 | '*'         | 20201224
CW8ISV4YIAS8CEIY8SNMYMSYB | 20201224 | '*'         | 20201224

What about the star itself:

  • As @LarsBr already said, in SQL you need to use LIKE '%pattern%' to search for strings contains parretn in the middle, % is equivalent for ABAP's * (but as far as I know * is more verbose placeholder in non-SQL world). So there's no out-of-the-box conversion of FIELD = '*' to FIELD like '%' or something similar.
  • But there's no LIKE predicate in Column Engine (in filter or in calculated column).

If you really need LIKE functionality in filter or calculated column, you can:

  • Switch execution engine to SQL 1
  • Or use match(arg, pattern) function of Column Engine, which now dissapeared from the pallete and is hidden quite well in the documentation (here, at the very end of the page, after digging into the description field of the last row in the table, you'll find the actual syntax for it. Damn!).

But here you'll meet another surprise: as long as Column Engine has different operators than SQL (it is more internal and more close to the DB core), it uses star (*) for wildcard character. So for match(string, pattern) you need to use a star again: match('pat string tern', 'pat*tern').

After all the above said: there are cases where you can really want to search for data with wildcards and pass them as parameter. But then you need to use match and pass the parameter as plain text without tricks on star (*) or something (if you want to use officially supported features, not trying to exploit some internals).

After adding this filter to RSPCLOGCHAIN projection node of my CV from the previous thread, it works this way:

1

do
begin
  select *
  from "_SYS_BIC"."ztest/CV_TEST_PERF"(
    PLACEHOLDER."$$P_DUMMY$$" => 'CW*'
  );
end;

LOG_ID                    | DATUM    | INPUT_PARAM | CUR_DATE
--------------------------+----------+-------------+---------
CW8ISV4YIAS8CEIY8SNMYMSYB | 20201224 | CW*         | 20201224


do
begin
  select *
  from "_SYS_BIC"."ztest/CV_TEST_PERF"(
    PLACEHOLDER."$$P_DUMMY$$" => 'CW'
  );
end;

/*
Fetched 0 row(s) in 0 ms 0 µs (server processing time: 0 ms 0 µs)
*/

Upvotes: 4

Lars Br.
Lars Br.

Reputation: 10388

The notation with triple quotation marks '''*''' is likely what yields the syntax error here.

Instead, use single quotation marks to provide the '*' string.

But that is just half of the challenge here.

In SQL, the placeholder search is done via LIKE and the placeholder character is %, not *.

To mimic the ABAP behaviour when using calculation views, the input parameters must be used in filter expressions in the calculation view. And these filter expressions have to check for whether the input parameter value is * or not. If it is * then the filter condition needs to be a LIKE, otherwise an = (equal) condition.

A final comment: the PLACEHOLDER-syntax really only works with calculation views and not with tables.

Upvotes: 1

Related Questions