Topic
5 replies Latest Post - ‏2013-02-05T22:14:13Z by DanielWagemann
SystemAdmin
SystemAdmin
1959 Posts
ACCEPTED ANSWER

Pinned topic XQE-DAT-0001 error when publishing package in DQM

‏2013-02-05T15:48:17Z |
I'm using Cognos 10.1.1 with oracle query DB. My report work fine in compatibility mode. When publishing the package in Dynamic Query Mode the report gives the error below. Any feedback greatly appreciated.
Data source adapter error: ORA-00905: missing keyword

=== JAVA STACK TRACE === XQE-DAT-0001 Data source adapter error. at com.cognos.xqe.data.providers.relational.jdbc.JDBCTabularResult.<init>(JDBCTabularResult.java:354) at com.cognos.xqe.data.providers.relational.jdbc.oracle.OracleConnection.getResultSetImpl(OracleConnection.java:142) at com.cognos.xqe.data.providers.relational.jdbc.JDBCConnection.getResultSet(JDBCConnection.java:1300) at com.cognos.xqe.data.providers.relational.jdbc.JDBCDataProvider.query(JDBCDataProvider.java:100) at com.cognos.xqe.data.providers.relational.TraceRelationalDataProvider.query(TraceRelationalDataProvider.java:125) at com.cognos.xqe.runtree.relational.XSql.executeImpl(XSql.java:291) at com.cognos.xqe.runtree.XNode.execute(XNode.java:344) at com.cognos.xqe.runtree.relational.decoration.XValueDecoration$XValueDecorationResult.<init>(XValueDecoration.java:375) at com.cognos.xqe.runtree.relational.decoration.XValueDecoration.executeImpl(XValueDecoration.java:167) at com.cognos.xqe.runtree.XNode.execute(XNode.java:344) at com.cognos.xqe.runtree.XTree.executeImpl(XTree.java:92) at com.cognos.xqe.runtree.XNode.execute(XNode.java:344) at com.cognos.xqe.runtree.XTree.execute(XTree.java:112) at com.cognos.xqe.rsapi.RSAPIDataset.executeRetry(RSAPIDataset.java:733) at com.cognos.xqe.rsapi.RSAPIDataset.execute(RSAPIDataset.java:663)
Updated on 2013-02-05T22:14:13Z at 2013-02-05T22:14:13Z by DanielWagemann
  • DanielWagemann
    DanielWagemann
    850 Posts
    ACCEPTED ANSWER

    Re: XQE-DAT-0001 error when publishing package in DQM

    ‏2013-02-05T16:27:37Z  in response to SystemAdmin
    See if you get any further clues from a validate of the report in IBM Cognos Report Studio.

    If you do not get any more clues, try removing one query at a time until the offending query has been identified.

    Then go through the query items removing one at a time until the error goes away.

    Also what JDBC driver are you using and what is the version of Oracle.
    • SystemAdmin
      SystemAdmin
      1959 Posts
      ACCEPTED ANSWER

      Re: XQE-DAT-0001 error when publishing package in DQM

      ‏2013-02-05T18:46:27Z  in response to DanielWagemann
      Hi Daniel,

      The report validats just fine, no errors. The follwing is the query item that generates the error, without it the report works fine

      cast(sysdate(), date)

      Is this not supported in dynamic mode? are there any functions not supported in DQM?

      I have oracle 11G, using ojdbc5.jar driver file.
      • DanielWagemann
        DanielWagemann
        850 Posts
        ACCEPTED ANSWER

        Re: XQE-DAT-0001 error when publishing package in DQM

        ‏2013-02-05T20:03:27Z  in response to SystemAdmin
        In theory, the report should run in both query modes without too much manual intervention. In practice the DQM has tighter rules when it come to query/data item expression creation.

        Sysdate is an Oracle function which returns the current date time of the database. Then a cast function is used to try and convert it into a date datatype, but Oracle prefers to deal with datetimes... a while back Oracle date columns were really date times with a mask applied. So now you are trying to do a local cast of an vendor specific function.

        There is a common function named current_date which does the same thing and is supposed to return a date field. What happens when you replace your old expression with current_date?
        • SystemAdmin
          SystemAdmin
          1959 Posts
          ACCEPTED ANSWER

          Re: XQE-DAT-0001 error when publishing package in DQM

          ‏2013-02-05T21:27:35Z  in response to DanielWagemann
          Daniel,

          Using current_date instead of sysdate() works!

          I wonder whether DQM will work with any of the Oracle DB functions! If not, this would be a major restriction that does not exist in compatibility mode!

          Can anyone confirm?
          • DanielWagemann
            DanielWagemann
            850 Posts
            ACCEPTED ANSWER

            Re: XQE-DAT-0001 error when publishing package in DQM

            ‏2013-02-05T22:14:13Z  in response to SystemAdmin
            I have seen other Oracle functions work without issue. The only thing to watch for is the combination of vendor-specifc functions with common functions that the database does not support. That beind said, sysdate can be a bit of an odd cat.