I'm having a difficult time understanding the proper syntax to be used when imbedding context variables in SQL queries. I have a stored query that looks like the following: select dAgentPerformanceStat.Timestamp,dAgentPerformanceStat.AgentLogin from dAgentPerformanceStat where dAgentPerformanceStat.Timestamp = to_date(DATEADD('dd',-1, sysdate),'YYYY-MM-DD') Note: the DATEADD intrinsic simply subtracts one day from the current system date. This query is for a Cache' database and when executed in SQL Builder it works just fine. to_date is a Cache' function with the following format: TO_DATE(date_string) My ultimate goal is to use a context variable to replace the 'DATEADD' function and then pass a date value in via command line (shell script) execution. Would seem pretty simple. So, I created a context variable called StartDate. It is of type String and its default value is set to "2013-08-26" I then modified the select statement in the stored query as follows: select dAgentPerformanceStat.Timestamp,dAgentPerformanceStat.AgentLogin from dAgentPerformanceStat where dAgentPerformanceStat.Timestamp = to_date(" + context.StartDate + ",'YYYY-MM-DD') I used this format because I've seen other examples on this forum that are similar. However, I've tried other syntaxes and have not been successful. The other context variables I've declared for the database connections work just fine. I'm thinking I may be missing something about how context variables are suppose to be used. One other thing to note is that when I press CTRL-space to bring up context variables in SQL Builder I don't see a StartDate as a selectable choice. Yet, it shows up just fine on the Contexts tab for the Job this query is part of. Is this because its not in a context group? Also, I would expect when executing the query in SQL Builder that the 'results' window would show the resulting query with the value of the context variable translated into the query. This is not the case. The query shown in the 'results' window is an exact copy of the query above with the + context.StartDate string included. What am I doing wrong?
I don't use SQL Builder myself but isn't it independent of the jobs so how can it recognise context variables? I think the problem with your modified query is that you're missing the single-quotes used to delimit dates in SQL - a common mistake. Try: select dAgentPerformanceStat.Timestamp,dAgentPerformanceStat.AgentLogin from dAgentPerformanceStat where dAgentPerformanceStat.Timestamp = to_date('" + context.StartDate + "','YYYY-MM-DD')
Thanks for the suggestions, however, neither one has solved my problem. I think there may be an issue with 'context mode' that I'm not understanding. In SQL Builder, regardless of whether 'context mode' is on or off the following query is successful. select dAgentPerformanceStat.Timestamp from dAgentPerformanceStat where dAgentPerformanceStat.Timestamp = to_date('2013-08-25','YYYY-MM-DD') There are no leading or trailing double quotes. When encompassing the query in double quotes I get ODBC errors that read:
This occurs regardless of whether context mode is on or off. So, it would appear encompassing the entire sql query in quotes is invalid. Not sure if this is a Cache thing or not. In Context mode: select dAgentPerformanceStat.Timestamp from dAgentPerformanceStat where dAgentPerformanceStat.Timestamp = to_date('" + context.Start_Date + "','YYYY-MM-DD') produces a 'General error' dialog. When I go into the code view the select statement looks like the following: String dbquery_tDBInput_1 = select dAgentPerformanceStat.Timestamp from dAgentPerformanceStat where dAgentPerformanceStat.Timestamp = to_date('" + context.Start_Date +"'','YYYY-MM-DD');
globalMap.put("tDBInput_1_QUERY",dbquery_tDBInput_1); Notice there are no double quotes surrounding the select statment. In the code view there is a Red circle with an imbedded white 'X' next to the 'String' line. The details associated with this error read: Multiple markers at this line - Syntax error on token "dAgentPerformanceStat", ; expected - dAgentPerformanceStat cannot be resolved to a type - select cannot be resolved to a variable - Syntax error, insert ";" to complete LocalVariableDeclarationStatement - Invalid character constant - Syntax error on token ".", , expected - where cannot be resolved to a type Its almost like each field associated with dAgentPerformanceStat is expected to be included as a context variable.
When adding the double quotes around the select statement: "select Timestamp from dAgentPerformanceStat where Timestamp = to_date('" +context.Start_Date + "','YYYY-MM-DD')" I continue to get the same ODBC error, however, in the code view the errors go away. When the double quotes are removed the code view errors return. This suggest the double quotes are needed. I would assume the quotes are needed to allow Talend to preprocess the entire string properly when context variables are being used. But the quotes seem to be being passed along to the ODBC drivers and it looks like they are choking on it. Is the the right interpretation? Or is this an ODBC problem?
The quoting issue seems like an inconsistency in the studio i.e. SQL Builder does not expect quotes but the actual job does. So I would go with what you need for the job. If context.Start_Date is a String with value "2013-08-25" then I can't see why "select Timestamp from dAgentPerformanceStat where Timestamp = to_date('" +context.Start_Date + "','YYYY-MM-DD')" should not work if "select Timestamp from dAgentPerformanceStat where Timestamp = to_date('2013-08-25','YYYY-MM-DD')" does since you should simply be replacing 2013-08-25 with " +context.Start_Date + ". Have you tried putting the above directly into your input component rather than using SQL Builder?
Amazing. placing the exact same query, with surrounding quotes, into the component works perfectly. Yet the query in SQL Builder does not. Good lesson to learn. This would appear to be a flaw in SQL Builder. Hopefully Talend can address this sometime in the future. Thanks so much for you help. I'm good to go.