I’m getting this error when i run the report. i’ve run the query in toad and it doesn’t give me any errors there. Any ideas as to where i should check next or what might be the cause of this?
Thanks!
mrelaxed (BOB member since 2007-02-02)
Hi mrelaxed,
The error description is as follows:
ORA-01722: invalid number
Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.
Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character “E” or “e” and retry the operation.
I’d look at the data type of the object you’ve created.
Make sure that you test the sql against the same data in toad and BO.
Hope this helps,
pingate 
(BOB member since 2006-06-01)
I have the same problem.
But there is nothing like violating the number data type field.
i have checked all the objects but everything is fine.
And the problem is when i validate the SQL query of the tab it is giving an error call Invalid type column (3)
lampack93 
(BOB member since 2008-05-23)
Try removing objects till you come to the one causing the problem. It could be a number type which has been set wrongly as charector in the dimension properties in the universe- or some issue like that.
Roshknee (BOB member since 2007-10-29)
I have identifed one of the object which is a number and the description is as
to_char(DateVariable,‘YYYY’)
i have changed it to
to_number(to_char(DateVariable,‘YYYY’)
But the above object is not creating the problem.
When i remove a condition between two Date type objects , then i am not getting the error. Please help me
lampack93 (BOB member since 2008-05-23)
I have encountered a similar issue with a date (in Oracle).
The error is triggered when I use two objects in combination (a string dimension and a date) however when I use either one on its own, no error is encountered.
The same query compiles in TOAD with no problem.
jacquib (BOB member since 2008-08-17)