Print Zero o Null if no data

hi

Can anyone please give me a solution as how to print a null or 0 or '' value in a matrix report
when there are no data to be retrieved i mean if some recods has no data how can i display 0 as a value ? Coz i want to do calculations on tht 0

thank you.
nour911Asked:
Who is Participating?
 
Wasim Akram ShaikCommented:
hi nour,

i think you have not read my comments clearly

select count(*) always return a value >0 if there are any rows, if there are no rows then it will zero, ie., the least value returned by count is 0.

0
 
Kelvin SparksCommented:
For the field you are reporting, use = Nz([fieldname],0)

Kelvin
0
 
Kelvin SparksCommented:
That was for an Access database - what SQL language are you using?

SQL Server is   ISNULL([fieldname],0)

Oracle is   =NVL([fieldname],0)

Kelvin
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
nour911Author Commented:


im using Oracle report builder ver 10g
0
 
nour911Author Commented:
NVL is not working, coz no null valued returned, there are no data
0
 
Kelvin SparksCommented:
I don't know the report builder syntax, but the NVL statement should work in the datasource (view, select etc...)
0
 
Kelvin SparksCommented:
Do you mean (by no data) that SELECT * FROM yourdatasource return no records?
0
 
nour911Author Commented:
i have select so many rows some have value some return no data ( Not null) there are not same.

i want to replace the no data with zero , i dont know how can be done.

in another scenario i used select count(*), i count > 0 then there are records if count(*) < 0 means no records
0
 
nour911Author Commented:
yes i mean by no data, if i do select it is return no recods
0
 
Wasim Akram ShaikCommented:
i think you are confused with count query

select count(*) always return a value >0 if there are any rows, if there are no rows then it will zero, ie., the least value returned by count is 0.

Count function will always return a value >=0 but not less than that.. i think you should correct your query instead...!!!

so definitely <0 will always fail...

0
 
Kelvin SparksCommented:
Generally in a database a row without data does not exist. A select statement can return 0 rows (no data). I don't know Oracle's report builders, but most has some form of functionality to handle cases where the datasource has not returned data. You need to look there for a solution.


Kelvin
0
 
ytarkanCommented:
In Oracle a select into statement that fetches no rows raises an NO_DATA_FOUND exception, you can put your select statement into and BEGIN END block and catch the no data found exception, assign the value you want (i.e 0 or NULL in your case) like this


function .....

    myvar1 number;
BEGIN
......
......

    BEGIN
      SELECT .... INTO MyVar1 FROM .... WHERE ....;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
          MyVar1 := 0;
      WHEN OTHERS THEN
           raise_application_error(SQLCODE, SQLERRM, true);
    END;
.....
.....
.....
0
 
nour911Author Commented:
Im using matrix report
The cell field not has all the time data, i want to do assign some value where there is no data in that cell.
The value is not a zero or fix data the value i want to replace is based on calculation.
I hope this will be clear :)
0
 
nour911Author Commented:
Mr wasimibm
Select count(*)  not all the time return > 0
if there are no records it will return zero
0
 
aftabazizCommented:
If you are using oracle Reports then there are 2 ways to do it:

1. Use NVL in query like this select NVL(FIELD,0) from TABLE

OR

2. Open Report in Oracle Reports, go to Layout Model, select field and select property palette and set Property of Value if Null = 0
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
Wasim Akram ShaikCommented:
hi,

what author has asked has been answered in my comment http:#a37382374, but the author didn't get back on this.. so we can't conclude that there is not enough information.. please close it differently,...
0
 
Wasim Akram ShaikCommented:
point no:3

 http:#a37382374 as an answer..!!

as author has asked for the question print zero or null

the count(1) returns 0 when the condition is fulfilled.. so print zero (question asker mentioned) has got it..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.