Error on SQL Query using a variable as the end of a table
Hello Everyone,
I am trying to automate one process using the global variables in a SQL query to upload a dataset in my flow.
My Global variables look like this :
{
"projectKey": "",
"projectOwner": "",
"vue": "2",
"debut_quad": "2022-05-01",
"fin_quad": "2022-08-31"
}
The automation is working with my date variables as they are used as "complete variables", meaning they are not "joined" to the part of an other string.
However, I am encountering a problem on my "vue" variable as I use it as a parameter in the of a table I want to query.
The query is working like this :
TABLE1 A
INNER JOIN TABLE2_'${vue}' P ON P.ID=A.ID
I need to update the vue variable depending on when I running my query. Sometime it will be 1, sometime 2, etc.
But as the variable is "the end" of my Table, Dataiku doesn't seem to understand it and rise the following error :
SQLException: [Teradata Database] [TeraJDBC 17.00.00.03] [Error 3707] [SQLState 42000] Syntax error, expected something like an 'ON' keyword between the word 'TABLE2_' and the string '2'.
Any idea how I could getting around the error ?
A lot of thanks for reading me !
Hadrien
Operating system used: Windows
Best Answer
-
JordanB Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 296 Dataiker
Hi @Sv3n-Sk4
,I was able to get this working by putting the whole expression in double quotes and removing the quotes around the variable as shown below.
SELECT * FROM "LINEAR_REGRESSION_car_data_${year}"
Please let us know if that does not work.
Thanks!
Jordan
Answers
-
Sv3n-Sk4 Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 32 ✭✭✭✭
Hi @JordanB
,
It's working perfectly, thanks so muchSo maybe it's me who didn't well understand the use of variables but as it was explained in the different courses I didn't know we can use it as you did.
I thought we had to use it everytime as this way '${variable}'!
Thanks again for your time and answer!Hadrien