Input parameters filled at runtime in SAP HANA SQL?

[christoph_glania]

Hi,

I got a graphical calculation view with a mandatory input parameter.

I want to write an SQL statement in a way that the value I pass to the input parameter isreadfrom another view  at runtime.

So instead of this:

select* from view1

with parameters

  'PLACEHOLDER' = ( '$$parameterA$$', 'Value');

I want to do this:

select* from view1

with parameters

  'PLACEHOLDER' = ( '$$parameterA$$', (select "columnB" from view2 where ....));

However, the system responds "sql syntax error: incorrect syntax near "(" "

Any ideas, or this this a restriction of HANA SQL?

Thanks in advance and best regards,

Christoph

[former_member182114 · Accepted solution]

Hi Christoph,

There's 4 types of input parameters.

Column, Static List, Derived from table and direct.

See more on SAP HANA Modeling guide

The use case you expose is to use the "Derived from table" where you have the oportunity to configure the source table direct on the view.

Regards, Fernando Da Rós

[former_member182302 · Accepted solution]

Hi Fernando Ros,

"Derived from table" will help in getting List of Values (LOV) when asked for "help" on that input parameter from reporting tools like in BO Analysis office or other tools.

But what Christoph is mentioning is that if we want to write SQL query for the same can he write in the manner mentioned above, is the syntax allowed i.e subquery allowed inside placeholder or only "Value" should be kept?

select* from view1

with parameters

PLACEHOLDER' = ( '$$parameterA$$', (select "columnB" from view2 where ....));

Am unable to get the syntax working too as it expects only a value there.

Regards,

Krishna Tangudu

[former_member182114 · Accepted solution]

Hi Krishna Tangudu,

The usage about LUV is:

Column - HANA provide to clients a distinct from column pointed

Static List - HANA provide to clients the static list modeled inside view

Direct - HANA can't provide help

Derived from table don't need an UI as it came from table (see below a sample of customizing it. You point the table, column and filtering options to reach the single value you will use):

@Cristoph,

The SQL you are trying to run is invalid. It's a placeholder and you need to pass values not another SQL.

Some time ago here on forum I saw a discussion of correct syntax for placeholder. Don't know if it's working properly now.

Regards, Fernando Da Rós

[former_member182302 · Accepted solution]

Thanks Fernandos for confirmation.

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

This message was moderated.

[acaireta]

Hi Christoph,

I think I can find the solution using a variable.

Example:

       --THIS NOT WORKS

       --select * from TEST_CALL_VIEW WITH PARAMETERS(PLACEHOLDER."$$itemcode$$" => :itemcode);

       --THIS WORKS

          select * from TEST_CALL_VIEW (PLACEHOLDER."$$itemcode$$" => :itemcode);

For more information see post:http://scn.sap.com/thread/3449885

Regards.

[former_member182114]

Hi Christoph,

Backing to your original problem, don't know if the input of called view can be from a table as this is also a bit hard-coded but in a view.

You can try declare a variable and use it on parameter place_holder.

select "columnB" into lv_colb from view2 where ...

select* from view1

with parameters

  'PLACEHOLDER' = ( '$$parameterA$$', :lv_colb);

Regards, Fernando Da Rós