topic Re: [resolved] Compare date tMysqlInput to tOracle in Talend Studio

[resolved] Compare date tMysqlInput to tOracle

chichibio — Fri, 25 Nov 2016 16:38:50 GMT

Hi at all,
I have a query in tOracleInput :
select * from tablea join...
WHERE UPDATEDATETIME > to_date('"+globalMap.get("row2.UPDATEDATETIME")+"','dd-MM-yyyy hh24:mi:ss')
I take max(updatedatetime) from tableA -->store in variable--> and compare max(updatedatetime) of mysql with oracle in condition query.
So my flow is:
tMysqlInput-->tFlowToIterate-->tOracleInput-->tMap-->tMysqlOutput
My error:
Exception in component tOracleInput_1
java.sql.SQLDataException: ORA-01861: literal does not match format string
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:79)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:774)
at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:849)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1377)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:386)
at migra_da_oracle_804.uso_variabili_0_1.uso_variabili.tMysqlInput_1Process(uso_variabili.java:1534)
at migra_da_oracle_804.uso_variabili_0_1.uso_variabili.runJobInTOS(uso_variabili.java:2294)
at migra_da_oracle_804.uso_variabili_0_1.uso_variabili.main(uso_variabili.java:2151)
disconnected
Job uso_variabili ended at 17:00 25/11/2016.
where is my error?

format max(updatedate) on mysql:

thnx at all!

Re: [resolved] Compare date tMysqlInput to tOracle

JR1 — Fri, 25 Nov 2016 16:45:59 GMT

Hi
Please take a close look at the date format you gave in your tOracleInput component and compare this with the one you provide in your tMySqlInput component.
UPDATEDATETIME > to_date('" globalMap.get("row2.UPDATEDATETIME") "',' dd-MM-yyyy hh24:mi:ss') (must be Oracle notation)
compared to
" yyyy-MM-dd hh:mm:ss" (Java notation)
The correct Oracle notation would be DD-MM-YYYY HH24:MIS and the matching Java notation would be dd-MM-yyyy hh:mm:ss.
I am not sure if this is everything you need to resolve this. A type conversion between date and string may ne necessary as well.
Regards, Joachim
PS: you posted this issue twice. Could you please close the other one? It's https://www.talendforge.org/forum/viewtopic.php?id=54211

Re: [resolved] Compare date tMysqlInput to tOracle

vapukov — Sat, 26 Nov 2016 04:11:02 GMT

correct form must be something like :

UPDATEDATETIME > to_date('"+TalendDate.formatDate("dd-MM-yyyy HH:mm:ss",globalMap.get("row2.UPDATEDATETIME"))+"','dd-MM-yyyy hh24:mi:ss')

it mean UDATEDATETIME - have date format as on attached picture

Re: [resolved] Compare date tMysqlInput to tOracle

chichibio — Mon, 28 Nov 2016 10:44:33 GMT

Hi
Please take a close look at the date format you gave in your tOracleInput component and compare this with the one you provide in your tMySqlInput component.
UPDATEDATETIME > to_date('" globalMap.get("row2.UPDATEDATETIME") "','dd-MM-yyyy hh24:mi:ss') (must be Oracle notation)
compared to
"yyyy-MM-dd hh:mm:ss" (Java notation)
The correct Oracle notation would be DD-MM-YYYY HH24:MIS and the matching Java notation would be dd-MM-yyyy hh:mm:ss.
I am not sure if this is everything you need to resolve this. A type conversion between date and string may ne necessary as well.
Regards, Joachim
PS: you posted this issue twice. Could you please close the other one? It's https://www.talendforge.org/forum/viewtopic.php?id=54211

Hi Joe,
thnx for your help.
Unfortunatley doesn't work. I try with to_date('" globalMap.get("row1.PXUPDATEDATETIME") "','yyyy-MM-dd hh24:mi:ss')
but I have another error: ORA-01830: date format picture ends before converting entire input string
ps.
Yes, I'm sorry. I'm getting close the other post.

Re: [resolved] Compare date tMysqlInput to tOracle

chichibio — Mon, 28 Nov 2016 10:47:55 GMT

correct form must be something like :
UPDATEDATETIME > to_date('"+TalendDate.formatDate("dd-MM-yyyy HH:mm:ss",globalMap.get("row2.UPDATEDATETIME"))+"','dd-MM-yyyy hh24:mi:ss')
it mean UDATEDATETIME - have date format as on attached picture

Hi Vapukov,
with your sintax I have another tipe of error:
the method formatDate(String,Date) in the type talendDate is not applicable for the arguments (String,Object)

Thnnk you so much
I need help :s

Re: [resolved] Compare date tMysqlInput to tOracle

vapukov — Mon, 28 Nov 2016 13:31:22 GMT

check the data types - it mean somewhere before You mark UPDATEDATETIME as Object

Re: [resolved] Compare date tMysqlInput to tOracle

chichibio — Mon, 28 Nov 2016 15:04:32 GMT

Hi...finally I'm resolved.
is necessary define string my result query : max(updatedatetime)

and in where condition :
PXUPDATEDATETIME > to_date('"+globalMap.get("row1.UPDATEDATETIME")+"','yyyy-mm-dd hh24:mi:ss')
thanx anyone to helped me!
best regard