OraFAQ Forum: Reports & Discoverer

Home » Developer & Programmer » Reports & Discoverer » Null Value (disc 10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Null Value [message #309512]

Thu, 27 March 2008 14:22

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

Report message to a moderator

Re: Null Value [message #309514 is a reply to message #309512]

Thu, 27 March 2008 14:35

Littlefoot
Messages: 21813
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"?

Report message to a moderator

Re: Null Value [message #309519 is a reply to message #309514]

Thu, 27 March 2008 14:46

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.

Report message to a moderator

Re: Null Value [message #309535 is a reply to message #309519]

Thu, 27 March 2008 16:07

Littlefoot
Messages: 21813
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;

Report message to a moderator

Re: Null Value [message #309601 is a reply to message #309535]

Fri, 28 March 2008 01:48

skooman
Messages: 913
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.

Report message to a moderator

Re: Null Value [message #309619 is a reply to message #309601]

Fri, 28 March 2008 03:09

Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Ooops! True! My apologies, I failed to notice "disc 10" and Discoverer-specific terms.

Report message to a moderator

Re: Null Value [message #309678 is a reply to message #309512]

Fri, 28 March 2008 07:51

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

Report message to a moderator

Re: Null Value [message #309680 is a reply to message #309678]

Fri, 28 March 2008 07:55

skooman
Messages: 913
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'))

Report message to a moderator

Re: Null Value [message #309687 is a reply to message #309680]

Fri, 28 March 2008 08:11

aarti81
Messages: 235
Registered: December 2007
Location: USA

Senior Member

Let me try and see if it works that way.

Thanks

Report message to a moderator

Previous Topic:	Help in Reports...
Next Topic:	Report error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 03 00:45:50 CDT 2024