Is there a way to use table name as a variable in SQL transform in Data Services?

[former_member680060]

I have defined $G_Table_Name as a global variable and then defined the table name through a script.

Upon trying to use it in my SQL transform I am getting syntax error. Following is the query I used:

Select * from Stage_Schema.[$G_Table_Name]

To check if it works like SAP Hana, I also tried the following but all of them give syntax error:

Select * from Stage_Schema.||[$G_Table_Name]

Can anyone point me to the right direction on this?

[klapo87]

Hello Rahul, I'm having exactly same issue where I require to pull same information from variable table depending on other data. One way would be to copy full query for all possible scenarios and make one big IF statement, but this would not be clean or nice to edit in future, but if desperate you can use below example:

DO BEGIN

IF 1=1 THEN

SELECT TOP 10 * FROM ORDR;

ELSE

SELECT TOP 10 * FROM OINV;

END IF;

END;

I'm more after something like

SELECT TOP 10 * FROM CASE WHEN({variable} = 1) THEN 'ORDR' ELSE 'OINV' END

but fighting it for way too many hours now in both HANA and Crystal and I'm not getting anywhere, not convinced it is possible to have variable table name that easily.

Hope someone had more luck!:)

Thanks!

Art

[werner_daehn]

The first version should create a valid SQL string. All other requirements must be met as well, e.g. the columns returned by the "select *" need to match the defined output schema precisely.

Generally speaking, neither using a SQL Transform as such nor a "select *" in it is advisable. It is a potential time bomb.

One option would be to drop/create a database view in the script where you set table variable and in the dataflow use the view as source. Thus you do not need the SQL Transform any longer and can benefit from all the optimizations BODS is capable of.

http://discoverbi.blogspot.com/2018/05/bods-sql-transform-with-variable.html