Search code examples
sqlexceptionsqldatatypesoracle-data-integrator

"Incompatible data type in operation" Error when trying to format datetime

In ODI, I am fetching a field [datetime] from a table which is in MS SQL server. The datetime from MS SQL is in this format: 2019-06-26 07:41:00.000.

I need to convert it to this format: 26-06-2019 10:41:00 i.e DD-MM-YYYY HH:MI:SS with hour incremented by 3, before writing it to a JSON file.

The corresponding field in JSON is VARCHAR(string).

But I am getting error: Caused By: java.sql.SQLSyntaxErrorException: incompatible data type in operation

I gave the following expressing in the ODI 12c mapping attribute expression editor:

TO_CHAR(TO_DATE(NVL(SUBSTR(L_MAILITM_EVENTS.EVENT_GMT_DT, 0, INSTR(L_MAILITM_EVENTS.EVENT_GMT_DT, '.')-1), 
L_MAILITM_EVENTS.EVENT_GMT_DT ),'YYYY-MM-DD HH24:MI:SS')+ 3/24, 
'DD-MM-YYYY HH24:MI:SS')

enter image description here

The entire error:

ODI-1228: Task Insert new lines-IKM XML Control Append- fails on the target connection IPSEvents_Array_Json. Caused By: java.sql.SQLSyntaxErrorException: incompatible data type in operation at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source) at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source) at com.sunopsis.jdbc.driver.xml.SnpsXmlStatementRedirector.execute(SnpsXmlStatementRedirector.java:77) at com.sunopsis.jdbc.driver.xml.SnpsXmlStatement.execute(SnpsXmlStatement.java:31) at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:205) at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:142) at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:28) at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:52) at oracle.odi.runtime.agent.execution.SessionTask.processTask(SessionTask.java:206) at oracle.odi.runtime.agent.execution.SessionTask.doExecuteTask(SessionTask.java:117) at oracle.odi.runtime.agent.execution.AbstractSessionTask.execute(AbstractSessionTask.java:886) at oracle.odi.runtime.agent.execution.SessionExecutor$SerialTrain.runTasks(SessionExecutor.java:2225) at oracle.odi.runtime.agent.execution.SessionExecutor.executeSession(SessionExecutor.java:610) at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:718) at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:611) at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:203) at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor.doProcessStartAgentTask(TaskExecutorAgentRequestProcessor.java:800) at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$1400(StartSessRequestProcessor.java:74) at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:702) at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:180) at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:108) at java.lang.Thread.run(Thread.java:748) Caused by: org.hsqldb.HsqlException: incompatible data type in operation at org.hsqldb.error.Error.error(Unknown Source) at org.hsqldb.error.Error.error(Unknown Source) at org.hsqldb.FunctionSQL.resolveTypes(Unknown Source) at org.hsqldb.FunctionCustom.resolveTypes(Unknown Source) at org.hsqldb.ExpressionArithmetic.resolveTypes(Unknown Source) at org.hsqldb.FunctionSQL.resolveTypes(Unknown Source) at org.hsqldb.ExpressionLogical.resolveTypes(Unknown Source) at org.hsqldb.ExpressionOp.resolveTypesForCaseWhen(Unknown Source) at org.hsqldb.ExpressionOp.resolveTypes(Unknown Source) at org.hsqldb.FunctionCustom.resolveTypes(Unknown Source) at org.hsqldb.ExpressionArithmetic.resolveTypes(Unknown Source) at org.hsqldb.FunctionCustom.resolveTypes(Unknown Source) at org.hsqldb.QuerySpecification.resolveExpressionTypes(Unknown Source) at org.hsqldb.QuerySpecification.resolveTypesPartOne(Unknown Source) at org.hsqldb.RangeVariable.resolveRangeTable(Unknown Source) at org.hsqldb.QuerySpecification.resolveRangeVariables(Unknown Source) at org.hsqldb.QuerySpecification.resolveReferences(Unknown Source) at org.hsqldb.QueryExpression.resolve(Unknown Source) at org.hsqldb.ParserDML.compileInsertStatement(Unknown Source) at org.hsqldb.ParserCommand.compilePart(Unknown Source) at org.hsqldb.ParserCommand.compileStatements(Unknown Source) at org.hsqldb.Session.executeDirectStatement(Unknown Source) at org.hsqldb.Session.execute(Unknown Source) ... 22 more

Where am I going wrong?

Solution

  • As the target is a file, it is not an Oracle database doing the transformation but the internal HSQL database of ODI Studio or the ODI agent. The Oracle functions can not be used here but it can be replaced by HSQL functions. Here are the available functions : http://hsqldb.org/doc/guide/builtinfunctions-chapt.html.

    Alternatively an expression could be added to the mapping right after the L_MAILITM_EVENTS datastore to do the transformation on the source database using MS SQL specific functions. This would change the SELECT statement executed on MS SQL to directly apply the functions. Make sure to change the execution location of the expression to the source.