This discussion is archived
0 Replies Latest reply: Dec 2, 2012 8:21 AM by 977470 RSS

Column Formula with Error in OBIEE 11g

977470 Newbie
Currently Being Moderated
Hello,

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,

Griselda

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points