| Invalid Number...BUG?? [message #291016] |
Wed, 02 January 2008 12:54  |
jfrano
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:
CASE WHEN UPPER("R/Rslt - RESULTS".Value Translation) NOT LIKE '%UNABLE%' THEN ROUND(TO_NUMBER("R/Rslt - RESULTS".Value Translation)) ELSE TO_NUMBER(0) END
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?
Thanks
[Updated on: Wed, 02 January 2008 15:24] Report message to a moderator |
|
|
|
|
|
| Re: Invalid Number...BUG?? [message #291023 is a reply to message #291021] |
Wed, 02 January 2008 15:23  |
jfrano
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 |
skooman
Messages: 913
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
