Webi throwing error but view is valid

[jaburke]

Hi,
I created a view to utilize in a universe. When I do a select * from the view in sqlplus, it returns fine. No errors. When I add this view to a universe and then query off it, I am getting the ORA-01722 Invalid number error. Why is BOE throwing an error when the view is fine in sqlplus? I'm using Oracle.

The view definition looks similar to this:

select field1, field2, field3, field4, cast( to_number(substr(field3, 3, 4)||field4) AS NUMBER) AS bestpr
FROM
(select a.field1,
a.field2,
a.field3,
a.field4,
max(CAST( to_number(substr(a.field3, 3, 4)||a.field4) AS NUMBER)) OVER (PARTITION BY a.field2) AS maxpr
from table a
group by a.field2, a.field1, a.field3, a.field4
)
WHERE cast( to_number(substr(field3, 3, 4)||field4) AS NUMBER) = maxpr;

Any help would be appreciated.

Thanks!

 

[hilfy]

What type of database are you connecting to and what type of connection is your universe using?

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.