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

[Anonymous]

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')" 

[JR1]

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?

[TRF]

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

[Anonymous]

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.

[Anonymous]

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

[JR1]

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

[Anonymous]

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')" 

[TRF]

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

[Anonymous]

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"))+"'
"

   

[vapukov]

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