0 Replies Latest reply: Dec 2, 2012 10:21 AM by 977470 RSS

    Column Formula with Error in OBIEE 11g


      On Oracle Business Intelligence Enterprise Edition 11g, I am trying to create an analysis. The database is MySQL. In the database, there is a field of type varchar(10). This
      field shows a date. In OBIEE 11g, when creating the analysis, in the Criteria tab, I choose this column (varchar) and edit the formula of it. The operation that I want to do is:

      YEAR(CURRENT_DATE) - SUBSTRING("TableName"."ColumnName" FROM 1 FOR 4) where the ColumnName is the name of the column of type varchar(10)

      but I get an error: +[nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27002] Near <->: Syntax error [nQSError: 26012]+

      If I execute the same query directly on the MySQL database, I get the desired result without any error.

      Also, if I use the following formula, I also get an error:

      CAST((SUBSTRING("TableName"."ColumnName" FROM 1 FOR 4)) AS INTEGER)

      So, I am asking why the first query throws an error in OBIEE 11g but not when it runs directly on the MySQL database. Also, how can I convert in OBIEE 11g a string to integer?

      Thank you in advance,