topic Re: [resolved] Syntax error when using global variable in where clause in Talend Studio

[resolved] Syntax error when using global variable in where clause

Anonymous — Mon, 05 Dec 2016 18:50:33 GMT

I am trying to use a global variable as part of my where clause in a JDBCInput component. I want to select all records where the LOAD_TS (load date) is greater than the minUpdateDate (my global variable) --i.e. the last time the job ran.
I can't seem to get the syntax right, i think it should be something along these lines but i get the following errors. Can anyone assist?
"select * from OPUB_WXDM_TM_PD tm
where tm.LOAD_TS>
TO_DATE(' "
+ TalendDate.formatDate("MM/dd/yyyy HH:mm:ss", (Date)globalMap.get("minUpdateDate"))
+ " ', 'MM/DD/YYYY HH24:MIS')"

Re: [resolved] Syntax error when using global variable in where clause

JR1 — Mon, 05 Dec 2016 20:29:56 GMT

You have forgotten to post the errors you are getting. Can you please tell us how you fill the global variable? Do you really put a date in there?

Re: [resolved] Syntax error when using global variable in where clause

TRF — Mon, 05 Dec 2016 22:02:01 GMT

Hi,
What if you replace + " ', 'MM/DD/YYYY HH24:MIS')" by + " ', 'MM/DD/YYYY HH24:MIS')" ?

Re: [resolved] Syntax error when using global variable in where clause

Anonymous — Mon, 05 Dec 2016 22:14:43 GMT

Here is the error I am receiving. I do need to use the date to determine whether or not the job needs to run. It must be something with the way I'm formatting it
Start Job Vars:
Job Name: OPUB_WXDM_TM_PD_grayner
Job Hist ID: 3247
Min Update Date: Fri Jan 01 00:00:00 PST 2016
Min ID: 0
maxUpdateDate: 2016-11-30 00:00:00.0
Exception in component tJDBCInput_1
org.teiid.jdbc.TeiidSQLException: TEIID30068 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30068 The function 'TO_DATE(' 01/01/2016 00:00:00 ', 'MM/DD/YYYY HH24:MI S')' is an unknown form. Check that the function name and number of arguments is correct.

Re: [resolved] Syntax error when using global variable in where clause

Anonymous — Mon, 05 Dec 2016 22:19:11 GMT

Here is the format of my variable within the job as well

Re: [resolved] Syntax error when using global variable in where clause

JR1 — Tue, 06 Dec 2016 06:52:48 GMT

Which database type do you query? TO_DATE() is an Oracle-only function.

Re: [resolved] Syntax error when using global variable in where clause

Anonymous — Tue, 06 Dec 2016 18:15:24 GMT

In the query I am comparing a date (LOAD_TS) from a JDBC database to a global variable date (minupdatedate) which is coming from an oracle database so that is why I was using TO_DATE should I be formatting the LOAD_TS instead or am I just not using the right format?
LOAD_TS format: 2016-12-06 00:00:00 (JDBC)
Min Update Date: Wed Nov 30 00:00:00 PST 2016
within the JDBC component "select * from OPUB_WXDM_TM_PD tm
where tm.LOAD_TS>
TO_DATE(' "
+ TalendDate.formatDate("MM/dd/yyyy HH:mm:ss", (Date)globalMap.get("minUpdateDate"))
+ " ', 'MM/DD/YYYY HH24:MIS')"

Re: [resolved] Syntax error when using global variable in where clause

TRF — Tue, 06 Dec 2016 20:42:15 GMT

Try to remove spaces before and after the date.
What if you try the same when directly connected to the database (not from TOS)?
Regards,
TRF

Re: [resolved] Syntax error when using global variable in where clause

Anonymous — Thu, 08 Dec 2016 01:47:12 GMT

removing the spaces was part of the issue (thank you!).
I believe the underlying source database is mysql (belongs to an outside vendor), however there is a jdbc application layer on top of it. I am able to get the syntax for the query to work:
"select * from OPUB_WXDM_TM_PD
where load_ts >= DATE '"+TalendDate.formatDate("yyyy-MM-dd", (Date)globalMap.get("minUpdateDate"))+"'
"
BUT the greater than or equal to is not working because although the database object for the the LOAD_TS field is noted as 'DATE' it looks like its a timestamp. Is that possible (I am not familiar with mysql)? There appears to be this extra 2016-12-06 00:00:00.0 at the end. When I change the query to TIMESTAMP it still brings back all records. Any thoughts on why this is happening?
"select * from OPUB_WXDM_TM_PD
where load_ts >= TIMESTAMP '"+TalendDate.formatDate("yyyy-MM-dd HH:mm:ss.S", (Date)globalMap.get("minUpdateDate"))+"'
"

Re: [resolved] Syntax error when using global variable in where clause

vapukov — Thu, 08 Dec 2016 22:39:40 GMT

not sure - You are start from Oracle, then MySQL ...

but generally - You do not need worry about right parts, You must check You left parts
query send to database always as string so,

select * from OPUB_WXDM_TM_PD 
where load_ts >= '"+TalendDate.formatDate("yyyy-MM-dd", (Date)globalMap.get("minUpdateDate"))+"'

must be ok, if load_ts - date, You just need worry about proper form of string TalendDate.formatDate("yyyy-MM-dd", (Date)globalMap.get("minUpdateDate"))
do not need any additional functions

Re: [resolved] Syntax error when using global variable in where clause

Anonymous — Thu, 08 Dec 2016 22:44:27 GMT

i was able to reformat the input date and now good to go thank you!