OBIEE Date Errors and Fixes

Its been busy in the OBIEE world and I have been no exception but finally got some off time to get my blogs running again. Hope I can stay on this path for a while before I get too busy 🙂

You will be really amazed at the number of bugs we discover around dates either in Oracle products and the latest Oracle BI product, OBBIEE. Amazingly enough, there are great folks who also volunteer to find out workarounds before Oracle fixes it with a patch or a release.

Let me ask you a question in OBIEE at the presentation layer (lets use 10g version). What if you have two columns (both numeric fields or lets say currency fields stored as numbers) and you want to sum them up?

Do you really know the answer? Think twice. You might say Yes but later you uncover that null value in one field leads to the sum field to be also null even when there is a value in the second field.

This is not a bug but rather you got to understand the OBIEE functionality. How did you fix this ? By adding a IFNULL condition for both the currency fields to check for null and if null, assign 0 as value.

Its all good now.

Similarly, think about what happens when you have a date field in the database and you want to display it in the presentation layer. You just want to bring the field as is through a logical field in the rpd defined as varchar.

What do you think will happen in this case? Well, all characters in the date field are characters and we should be able to display them easily. Think again 🙂

I recently encountered this error when I was trying to display a date field in my reports.
The error reads like this:

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 46035] Datatype(TIMESTAMP) nullable(1) unicode(1) external char(0) conversion to datatype(VARCHAR) nullable(1) unicode(0) external char(0) is not supported. (HY000)

Does this error give you any good information. Indeed, it does but you have to carefully look for it. We are trying to display a date field and internally date is converted to a timestamp field in OBIEE and that is where we have the problem. Date field in the database is brought into the rpd as timestamp and we are trying to store that value in a varchar field.

How did we fix this? In your presentation layer, use the function for the date column and cast the column as a date like CAST(col1 as DATE). This should fix your issue. But, to prevent everyone spending hours trying to research this error, change the column in the rpd with this CAST function being applied and this will apply across all the presentation folders where we are using this column.

Until next time, kudos to all OBIEE evangelists out there trying to make this product better and valuable.