Table-Valued function as data source in TI

[mvaspal]

Hi,

does anyone know whether it's possible to use a table-valued function in the data source of a TI?
i.e.

select * from tfn_Test1 ('Param1','Param2')

At the moment I'm getting an SQL statement failing error

I remember I saw stored procedures working, but never tried table-valued functions

Thank you!

[tomok]

Hi,

does anyone know whether it's possible to use a table-valued function in the data source of a TI?
i.e.

select * from tfn_Test1 ('Param1','Param2')

At the moment I'm getting an SQL statement failing error

I remember I saw stored procedures working, but never tried table-valued functions

Thank you!

TM1 is generally not in the business of supporting or not supporting SQL commands. It's really more about whether the ODBC driver installed on your TM1 server supports it. Have you tried your query in another client (like Toad, or some other front-end) to make sure it works. If it is successful in returning rows via ODBC then it should work in TM1.

p.s. You may be able to find more clues in the tm1server.log file as to why it isn't working.

[mvaspal]

Thank you Tom

This is IBM cloud SaaS environment, so no chance to test it anywhere else than in the TI.

The function works fine locally, and our ODBC connection is also set up and running, can read from tables

Secure Gateway uses "ODBC Driver 13 for SQL Server"

Maybe I should have asked if anybody ever managed to use such a function from IBM Cloud via Secure Gateway

[mvaspal]

Thank you Tom again,
I can confirm it worked fine once I got a user who had access to all the necessary tables