Select data from HANA table function in SQL and generate select statement for table function

[former_member821662]

Hello Experts,

Can anyone answer below questions, I have already searched but unable to find answers.

1.How to select data from a table function in SQL console? select columns from table function name doesn't work

2.How to generate a select statement on table functions which should give me all the columns the table function is returning?

I am actually looking to debug table function(the calculation view where this table function is being used) but somehow the debug option in the calculation view does nothing. It works for other calculation views except this. Any pointers/inputs on this will also help. I am using BWoH 7.5 HANA Studio 2.3.53 (HANA DB version 2)

Thanks!

[Abhishek_Hazra · Accepted solution]

Hi mohammed.naveed1,

1. To select on the table function you have to provide the complete path of the location where table function is created in. Please follow the steps as mentioned - Go to the modeler perspective -> Open SQL console -> Go to Catalog -> Choose the schema you have the table function in (usually it is _SYS_BIC) -> Go to Functions folder -> find the table function -> drag it after the select statement in the SQL console with a pair of closed braces () at the end. If you have parameters to be passed on, place them inside the braces -> Execute.

2. You can not generate a select statement from a table function directly. Create a dummy graphical calculation view consuming just the table function & add all returned fields to output of the calculation view & activate. Hereafter you can generate a select statement from the calculation view, you can get all the column names in the generated statement as returned by the table function.

Hope this helps. 🙂

Best Regards,
Abhi

[former_member821662 · Accepted solution]

Great, thank you! I was missing on the "()" part after dragging the function name.

Now I have two parameters "IN_WK_FRM" and "IN_WK_TO and I would like to pass '001' and '008'.

I tried just with ('001', '008') but it gives me insufficient privilege error. Is this the correct syntax? If not, can you please provide the exact syntax to provide in the parenthesis

[Abhishek_Hazra · Accepted solution]

good 🙂 And your syntax looks correct. But since your error message says it is an authorization error, it has nothing to do with the syntax. Please check for analytic privileges if any in place for the tables used in the function. I would recommend to check with your access management colleagues.
And since your original question has been answered, could you please accept the answer? Thank you 🙂

Best Regards,
Abhi

[former_member821662 · Accepted solution]

It was indeed the correct and helpful answer, marked now.

Thanks!