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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
nour911Author Commented:


im using Oracle report builder ver 10g
0
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Qlemo"Batchelor", 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.