Current Position:Home > Substring in ODI

Substring in ODI

Update:11-30Source: network consolidation
Advertisement
Hi,
In my Oracle database I have Year in four digit format and i need to change it to FYXX format.
Column name in source is called ROK.
In my mapping (executed on staging table - sunopsis memory engine) I have:
'FY' || substr(XX_TKW_DANE_O_PROD.rok,3,2)
When executing interface I get following error:
org.apache.bsf.BSFException: exception from Jython:
Traceback (most recent call last):
File "<string>", line 30, in <module>
     at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
     at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
     at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
     at sun.reflect.GeneratedMethodAccessor404.invoke(Unknown Source)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
     at java.lang.reflect.Method.invoke(Method.java:601)
java.sql.SQLException: java.sql.SQLException: incompatible data type in operation
     at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
     at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
     at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
     at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2472)
     at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:47)
     at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:1)
     at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
     at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
     at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
     at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:558)
     at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:464)
     at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
     at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
     at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
     at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
     at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
     at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
     at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
     at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
     at java.lang.Thread.run(Thread.java:722)
Caused by: Traceback (most recent call last):
File "<string>", line 30, in <module>
     at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
     at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
     at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
     at sun.reflect.GeneratedMethodAccessor404.invoke(Unknown Source)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
     at java.lang.reflect.Method.invoke(Method.java:601)
java.sql.SQLException: java.sql.SQLException: incompatible data type in operation
     at org.python.core.Py.JavaError(Py.java:455)
     at org.python.core.Py.JavaError(Py.java:448)
     at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:177)
     at org.python.core.PyObject.__call__(PyObject.java:355)
     at org.python.core.PyMethod.__call__(PyMethod.java:215)
     at org.python.core.PyMethod.instancemethod___call__(PyMethod.java:221)
     at org.python.core.PyMethod.__call__(PyMethod.java:206)
     at org.python.core.PyObject.__call__(PyObject.java:397)
     at org.python.core.PyObject.__call__(PyObject.java:401)
     at org.python.pycode._pyx46.f$0(<string>:39)
     at org.python.pycode._pyx46.call_function(<string>)
     at org.python.core.PyTableCode.call(PyTableCode.java:165)
     at org.python.core.PyCode.call(PyCode.java:18)
     at org.python.core.Py.runCode(Py.java:1204)
     at org.python.core.Py.exec(Py.java:1248)
     at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:172)
     at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)
     ... 19 more
Caused by: java.sql.SQLException: incompatible data type in operation
     at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
     at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
     at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
     at sun.reflect.GeneratedMethodAccessor404.invoke(Unknown Source)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
     at java.lang.reflect.Method.invoke(Method.java:601)
     at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:175)
     ... 33 more
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.ExpressionArithmetic.resolveTypes(Unknown Source)
     at org.hsqldb.QuerySpecification.resolveExpressionTypes(Unknown Source)
     at org.hsqldb.QuerySpecification.resolveTypesPartOne(Unknown Source)
     at org.hsqldb.QuerySpecification.resolveTypes(Unknown Source)
     at org.hsqldb.QueryExpression.resolve(Unknown Source)
     at org.hsqldb.ParserDQL.compileCursorSpecification(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)
     ... 39 more
Can someone tell me what am I doing wrong?
Best regards,
Greg

The Best Answer

Advertisement
Hi John,
I am just learning. You are right that it is easier to be done in oracle.
I have one more question.
I am loading data always to the same account: 'Ilość produkcji (j. podstawowa)'.
I can only map this on SME (source and target gives error). I have "Unicode in data" option in IKM set to true.
However it reject all my rows with:
Unknown Member [IloϾ produkcji (j. podstawowa)]
Do you know why this ban be happening?
Best regards,
Greg
  • Substring in ODI Update:11-30

    Hi, In my Oracle database I have Year in four digit format and i need to change it to FYXX format. Column name in source is called ROK. In my mapping (executed on staging table - sunopsis memory engine) I have: 'FY' || substr(XX_TKW_DANE_O_PROD.rok,3

  • ODI Error while using substring function in odiref.getTable function Update:10-11

    Hi, I am trying to strip the interface table name in the IKM code.Below is the code create unique index      <%=odiRef.getTable("L","INT_NAME","W").substring(0,20)%> *on          <%=odiRef.getTable("L",&quo

  • Getting a substring of an ODI Variable Update:11-30

    Hello Gurus I am trying to get a substring of an ODI variable so that I can load the substring into the target datastore eg: client_name = substr(#project.file_name,0,5). By the way this variable is a a dynamic file name Is this possible? Please help

  • Assigning a Jython variable value to an ODI variable Update:10-11

    I have to implement database cursor functionality in ODI. For storing the returned values from database, I am using a Jython list. The next functionality that I have to implement is as follows: (i) For each value in the Jython list, I have to pass th

  • Help using oracle syntax "SUM(col1) over (order by col2)" using ODI Update:10-11

    Hi all I want to load data from oracle to ESSBASE using ODI, and I know oracle have such syntax sum(col1) over (order by col2,col3) which can get the accumulation data, e.g Oracle data table col1, col2, value A 2009-1 10 A 2009-2 10 A 2009-3 10 And t

  • IllegalArgumentException  while executing a scenario in ODI Update:10-11

    Hi All, We are executing a scenario in ODI. This loads the data from source table to target table . During this execution, we are facing the issue in the insert step(Error msg given below): “java.lang.IllegalArgumentException: Bytes are too big for a

  • Assigning long text values to ODI variable Update:10-11

    Hi all, I want to store the error message of the previous step in a variable: _<%=odiRef.getPrevStepLog("MESSAGE")%>_ (using ODI 10.1.3.5 on Oracle 9i). If I refresh the variable using a statement like Select '<%=odiRef.getPrevStepLog(&

  • ODI-17517: Error during task interpretation. Task: 6 java.lang.Exception Update:10-11

    Hi, Can some one provide a resolution for below error: I have created an interface which load data from csv file which has 320 columns, to a Synonym which has 320 columns in it using LKM File to SQL, IKM Sql Control Append. I am getting below error w

  • Error message in ODI Update:10-11

    Hi, I am trying to insert error messsage in to the table using a procedure. To get the error, i am using API: odiRef.getPrevStepLog("MESSAGE") after the interface that raise the error, in a "KO" red line. But,Message will be very huge

  • Call oracle function in ODI Update:11-30

    I am new to ODI. I need to call a function created on the database which return a value. In ODI I created a variable. In the select (of the variable) I don't know how to call the source table value that needs to be passed to the function. My oracle f