5 Replies Latest reply on Oct 15, 2012 1:39 PM by MikeTurner_SATX

    Unexpected Token error loading data to Essbase with ODI for Planning App

    MikeTurner_SATX
      I'm new to Hyperion, ODI and Essbase. I'm trying to load data to Essbase and I think I keep getting this error:
      java.sql.SQLException: java.sql.SQLException: unexpected token: ||C6_FUND_CODE "Fund", : line: 17

      The ||C6_FUND_CODE is acutally ,'Fund_'||C6_FUND_CODE "Fund", in the sql generated below.

      I'm stumped and would appreciate any advice. Below is the full text from the ODI operator for the Definition and Code tabs.

      Thank you in advance,

      Mike

      ------------------------------------------- FROM DEFINITION TAB OF INTEGRATION TASK

      org.apache.bsf.BSFException: exception from Jython:
      Traceback (most recent call last):
      File "<string>", line 37, 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.NativeMethodAccessorImpl.invoke0(Native Method)

           at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

           at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

           at java.lang.reflect.Method.invoke(Method.java:597)


      java.sql.SQLException: java.sql.SQLException: unexpected token: ||C6_FUND_CODE "Fund", : line: 17

           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:2473)
           at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:48)
           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:561)
           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:83)
           at java.lang.Thread.run(Thread.java:662)
      Caused by: Traceback (most recent call last):
      File "<string>", line 37, 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.NativeMethodAccessorImpl.invoke0(Native Method)

           at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

           at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

           at java.lang.reflect.Method.invoke(Method.java:597)


      java.sql.SQLException: java.sql.SQLException: unexpected token: ||C6_FUND_CODE "Fund", : line: 17

           at org.python.core.PyException.fillInStackTrace(PyException.java:70)
           at java.lang.Throwable.<init>(Throwable.java:181)
           at java.lang.Exception.<init>(Exception.java:29)
           at java.lang.RuntimeException.<init>(RuntimeException.java:32)
           at org.python.core.PyException.<init>(PyException.java:46)
           at org.python.core.PyException.<init>(PyException.java:43)
           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._pyx21.f$0(<string>:46)
           at org.python.pycode._pyx21.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)
           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)
           ... 1 more
      Caused by: java.sql.SQLException: unexpected token: ||C6_FUND_CODE "Fund", : line: 17
           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.NativeMethodAccessorImpl.invoke0(Native Method)
           at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
           at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
           at java.lang.reflect.Method.invoke(Method.java:597)
           at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:175)
           ... 33 more
      Caused by: org.hsqldb.HsqlException: unexpected token: ||C6_FUND_CODE "Fund", : line: 17
           at org.hsqldb.error.Error.parseError(Unknown Source)
           at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
           at org.hsqldb.ParserDQL.XreadSelect(Unknown Source)
           at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
           at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
           at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
           at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
           at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
           at org.hsqldb.ParserDQL.XreadQueryExpression(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)
           ... 40 more



      ------------------------------------------- FROM CODE TAB OF INTEGRATION TASK

      from com.hyperion.odi.common import ODIConstants
      from com.hyperion.odi.connection import HypAppConnectionFactory
      from java.lang import Class
      from java.lang import Boolean
      from java.sql import *
      from java.util import HashMap
      #
      # Get the select statement on the staging area:
      #
      sql= """select 'AC_'||C2_ACCOUNT "Account",CASE      WHEN C3_ACCOUNTING_PERIOD = '0' THEN 'Sep'
           WHEN C3_ACCOUNTING_PERIOD = '1' THEN 'Sep'
           WHEN C3_ACCOUNTING_PERIOD = '2' THEN 'Oct'
           WHEN C3_ACCOUNTING_PERIOD = '3' THEN 'Nov'
           WHEN C3_ACCOUNTING_PERIOD = '4' THEN 'Dec'
           WHEN C3_ACCOUNTING_PERIOD = '5' THEN 'Jan'
           WHEN C3_ACCOUNTING_PERIOD = '6' THEN 'Feb'
           WHEN C3_ACCOUNTING_PERIOD = '7' THEN 'Mar'
           WHEN C3_ACCOUNTING_PERIOD = '8' THEN 'Apr'
           WHEN C3_ACCOUNTING_PERIOD = '9' THEN 'May'
           WHEN C3_ACCOUNTING_PERIOD = '10' THEN 'Jun'
           WHEN C3_ACCOUNTING_PERIOD = '11' THEN 'Jul'
           WHEN C3_ACCOUNTING_PERIOD = '12' THEN 'Aug'
           WHEN C3_ACCOUNTING_PERIOD = '998' THEN 'Aug'
           WHEN C3_ACCOUNTING_PERIOD = '999' THEN 'Aug'
           ELSE 'Aug'
      END "Period",C4_FISCAL_YEAR "Year",'Actual' "Scenario",'Entry' "Version",'C5_DEPTID "Entity",'Fund_'||C6_FUND_CODE "Fund",'PID_'||C7_PROJECT_ID "PID",C1_DATA "Data" from "C$_0HYPPBR_PlanBudData" where      (1=1) """

      srcCx = odiRef.getJDBCConnection("SRC")

      stmt = srcCx.createStatement()

      srcFetchSize=30

      #stmt.setFetchSize(srcFetchSize)
      stmt.setFetchSize(1)
      print "executing query"
      rs = stmt.executeQuery(sql)
      print "done executing query"

      #load the data
      print "loading data"
      stats = pWriter.loadData(rs)
      print "done loading data"
      #close the database result set, connection
      rs.close()
      stmt.close()