Data Services / Data Integrator Global variable in SQL Transformation

[Former Member]

Hi

I want to use a global variable inside a SQL Transformation, but I got an error in the runtime. This makes me think there is no preprocessing of the text inside the Transformation. Is there a way to work around this ?

Can I change dynamically the contents of the SQL Transformation ?

Example:

select productid, productname from products where year = $YearGV

I tried enclosing the variable with {} [] and () but with no luck.

Any help would be appreciated.

Paolo

[Former Member · Accepted solution]

you can use Global Variables in SQL Transfrom, what is the error you are getting ?

what is the datatype of Global variable ? since you are comparing with year then it should be Date or DateTime, and you have to enclose this in [ ]

 for example:- YEAR =  [$YearGV] 

if you enable the trace for SQL reader you can see the SQL Statement that is submitted to the database

you will not be able to Update Schema with refrences of global Variable since the variable substitution happens at runtime not at design time, so for update schema, use SQL Statment without Global variable, once schema is updated add the global variable

for example:

to update schema use

SELECT * FROM SCOTT.EMP

after update schema

SELECT * FROM SCOTT.EMP WHERE SAL > [$GV_SAL]

[Former Member · Accepted solution]

Enclosing with [] and updating schema without the global variable worked fine. Thank you very much