Home » Developer & Programmer » Reports & Discoverer » Null Value (disc 10)
Null Value [message #309512] Thu, 27 March 2008 14:22 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi

I have the following fields

field1 field2 field3 field4
Now when i run the report, its running good ecxcept for there are some null values in field3 which need to be replaces by "abc"
Any ideas or pointers as to tackel this. I know how to replace the null values for the whole work sheet , but here i just need to do that for field3.

Thanks
Re: Null Value [message #309514 is a reply to message #309512] Thu, 27 March 2008 14:35 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
I know how to replace the null values for the whole work sheet
How would you do that? How does it differ from applying the same solution only for "field3"?
Re: Null Value [message #309519 is a reply to message #309514] Thu, 27 March 2008 14:46 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
You have an option, in worksheet properties, where it asks you

Show NULL Value as :

But that replaces all the null values on the worksheet, not just a single field.
Re: Null Value [message #309535 is a reply to message #309519] Thu, 27 March 2008 16:07 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, you could use the NVL function, either in a query or by creating a formula column which will also use NVL and be used instead of the original "field3" field.

In a query:
SELECT field1, field2, NVL(field3, 'abc') f3
FROM some_table


Formula column:
FUNCTION f3 RETURN NUMBER
IS
  retval NUMBER;
BEGIN
  return NVL(:field3, 'abc');
END;
Re: Null Value [message #309601 is a reply to message #309535] Fri, 28 March 2008 01:48 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
The functionality that OP is refering to is the Discoverer specific functionality of showing all nulls (any workbook, any column) as a certain value. One can also choose something like '-' to show in that case.

For one specific column, indeed work with an NVL around it. The function LF shows will not be the first choice for Discoverer (can't be used directly by Discoverer, so it has to be defined in the database and then registered in Discoverer - that's a lot of work for something that comes out-of-the-box already Wink). You can directly use NVL in a Discoverer calculation.
Re: Null Value [message #309619 is a reply to message #309601] Fri, 28 March 2008 03:09 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ooops! True! My apologies, I failed to notice "disc 10" and Discoverer-specific terms.
Re: Null Value [message #309678 is a reply to message #309512] Fri, 28 March 2008 07:51 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Thank You all

But we can use the NVL for the same datatypes and here i'm replacing a number with text.

Thanks
Re: Null Value [message #309680 is a reply to message #309678] Fri, 28 March 2008 07:55 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Good point.

You're relying on implicit conversion then (that's when the database decised to perform a to_char, although you didn't explicitely tell it to do so).
Could be a bit tricky, for example when your calculation is used for other purposes sometime in the future), so it's better to do something like:
nvl(to_char(<your number column>,'abc'))
Re: Null Value [message #309687 is a reply to message #309680] Fri, 28 March 2008 08:11 Go to previous message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Let me try and see if it works that way.


Thanks
Previous Topic: Help in Reports...
Next Topic: Report error
Goto Forum:
  


Current Time: Fri Dec 09 02:12:00 CST 2016

Total time taken to generate the page: 0.12185 seconds