Home » Developer & Programmer » Reports & Discoverer » Invalid Number...BUG?? (10g)
Invalid Number...BUG?? [message #291016] Wed, 02 January 2008 12:54 Go to next message
Messages: 11
Registered: January 2007
Location: New York
Junior Member
I have a worksheet that is a subuery. The sub-query retrieves a list of Medical Record Numbers. With that the second worksheet is retrives LDL values: I've used the below as a CALCULATION for LDL:


This works fine, I get all numeric values and can even do addition to each of the values.

However, when I try to place a condition on the Calculated item LDL I get INVALID NUMBER the report aborts.

The condition is simple ie LDL >= 130

I really don't understand how the To__Number works...I can even add the LDL values, yet I can't do a condition! Has to be a bug

Any workaround?


[Updated on: Wed, 02 January 2008 15:24]

Report message to a moderator

Re: Invalid Number...BUG?? [message #291021 is a reply to message #291016] Wed, 02 January 2008 14:26 Go to previous messageGo to next message
Messages: 20850
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It sounds like a Discoverer question (and I'm not familiar with it). However, let me try: what is LDL's datatype? Should be a number. Because, if it is a character, something like this might run without an error: LDL >= '130' (note single quotes), but I'm not sure you really want to do that.

By the way, TO_NUMBER(0) doesn't make sense; 0 already IS a number.
Re: Invalid Number...BUG?? [message #291023 is a reply to message #291021] Wed, 02 January 2008 15:23 Go to previous messageGo to next message
Messages: 11
Registered: January 2007
Location: New York
Junior Member
Thanks for the reply...
Yes, this is a Discoverer issue.

You don really declare the datatye, when defining a variable in Discoverer; however when storeing the result in LDL using the TO_NUMBER function it has to be a number.

I used the To_NUMBER on the 0 when I couldn't figure out what was going on and wanted to ensure any value stored in LDL was a number, which it is. That's why I even created another calculation in which I added 1 to LDL, which worked. Thus the puzzlement in when I do a conditon on LDL >= 130 aborting with INVALID NUMBER...tried 130 with and without quotes to be sure.

Re: Invalid Number...BUG?? [message #291237 is a reply to message #291023] Thu, 03 January 2008 08:56 Go to previous message
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
I would guess that there is some implicit conversion going on somewhere. One way or another, you only hit the invalid number error when you add a condition to it. But, when you create a calculation on top of it, that works, could be that you're only selecting like the first N records, so you might run into the same error if you retrieve all rows or something.

Anyway, what is the datatype of "R/Rslt - RESULTS".Value Translation?
(look it up in the database please, but also check the EUL, perhaps there is some sort of transition).

To be sure that it's no Discoverer bug, can you please test something? Write a SQL script with something like:

select count(*)
from <table where value transition is in>
where <your case when statement here> >= 130

Run this in SQL*Plus and please copy paste the result here.
Previous Topic: Problem with Place holder columns
Next Topic: how to Add Image In Oracle apps reports
Goto Forum:

Current Time: Sat Oct 22 19:32:49 CDT 2016

Total time taken to generate the page: 0.07647 seconds