Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: What is the NULL Inidicator value if SUM() returns zero
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    Post subject:
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    Post subject: Reply to: What is the NULL Inidicator value if SUM() returns
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    Post subject:
Reply with quote

If you are getting negative values, it should be null values being returned... ( http://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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DFHRESPONSE returns issue divated CICS 3 Wed Nov 02, 2016 6:32 pm
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm
No new posts GetObjectArrayElement returns NULL Majid Hussain Java & MQSeries 2 Wed Mar 30, 2016 2:59 am
No new posts Query to select Null row on priority RahulG31 DB2 24 Thu Jan 21, 2016 5:45 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us