IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

What is the NULL Inidicator value if SUM() returns zero


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
praveenhu

New User


Joined: 23 Jun 2006
Posts: 5

PostPosted: Wed Sep 12, 2007 11:37 am
Reply with quote

Hi,

The query looks some thing like this:
Select sum(abc)
into :xyz:xyz-n
from tablename;

If the sum() function returns Value zero, then wat will be the NULL Indicator value.

Please help me in this, this is of high priority to me icon_sad.gif
Back to top
View user's profile Send private message
achusarath

New User


Joined: 21 Mar 2007
Posts: 26
Location: kerala

PostPosted: Wed Sep 12, 2007 12:02 pm
Reply with quote

value will be zero since the result is zero which is a value in column .
Back to top
View user's profile Send private message
praveenhu

New User


Joined: 23 Jun 2006
Posts: 5

PostPosted: Wed Sep 12, 2007 4:03 pm
Reply with quote

But in my case, its returning <0 value; After sql i am checking whether the NULL indicator < 0 and the sum() value = 0, its satisfying the condition... Even i am confused how is this possible...

Looking for the reply...

Thanks for the quick response
Back to top
View user's profile Send private message
Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Wed Sep 12, 2007 4:58 pm
Reply with quote

If you are getting negative values, it should be null values being returned... ( publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.embed.doc/doc/t0005636.htm)

You can use the function SUM(COALESCE(abc,0))..so that null will be replaced by 0s if any, by coalesce..and also you might not need the null indicator also...
I am not sure whther this will be ok for ur reqmt..Any check it out..


[/url]
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Wed Sep 12, 2007 8:41 pm
Reply with quote

you can see the difference with the sample query against the QMF sample tables.

SELECT SUM(DEPT) FROM Q.STAFF WHERE DEPT = 10

&

SELECT SUM(DEPT) FROM Q.STAFF WHERE DEPT = 99

I put it like this way. When there is an aggregate function is used in a query and if there are no rows satisfying the WHERE predicate, then DB2 will give NULL value. Its same with MAX,MIN,AVG,SUM and etc...but not with COUNT..!!

Confused..? Then you should refer to manual. I may not be clear in explaining.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Null values are considered in Total c... DFSORT/ICETOOL 6
No new posts the system or user abend SF0F R=NULL COBOL Programming 0
No new posts Uploading from desktop, a CSV file, s... DB2 2
No new posts How to handle NULL in COBOL program COBOL Programming 8
No new posts Date insertion returns -180 CICS 1
Search our Forums:

Back to Top