View previous topic :: View next topic
|
Author |
Message |
sonali12_9
New User
Joined: 13 Feb 2009 Posts: 20 Location: United States of america
|
|
|
|
I am running one query and some of the fields are having null values. Now my requirement is i want to sum the unit cost field depending of part number.
Unit cost field is packed decimal S9(6)v9(03) comp-3.
If i try to sum then i am getting Soc 7.
How to replace null values with 0.
I am using Syncsort |
|
Back to top |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009 Posts: 2481 Location: Netherlands, Amstelveen
|
|
|
|
Gentlemen, the TS is using SYNCSORT as far as i could see. |
|
Back to top |
|
|
abhishek.sun
New User
Joined: 22 Aug 2008 Posts: 2 Location: Gurgaon
|
|
|
|
enrico-sorichetti wrote: |
Mukesh,
both ideas are just plain wrong...
they just show that You do not have the slightest idea of what You are talking about
did You ever care to look at the db2 manuals for the meaning of NULL
NULLness is a concept not a value
meditate, please... meditate
ibmmainframes.com/viewtopic.php?t=45562&highlight=null |
Hey enricho,
I looked at the link you mentioned.
You explained NULL very well. by saying:
"to distinguish between low values and NULL value
every field must be defined os two tokens...
a flag and a value
the flag vill tell if the value has been assigned ( NOT NULL ) or not ( NULL)
so that You can process accordingly"
I knew the concept but it is very good for one who is learning.
Regards,
Abhishek |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
I may have gone a bit overboard, but i've changed the title to include "Syncsort" and removed most of the non-sort-related db2 replies.
On the original question, the 0c7 is due to incorrectly defined data or invalid data content.
If the data contails x'000000' and should be packed decimal, chnge it to x'00000C'. |
|
Back to top |
|
|
sonali12_9
New User
Joined: 13 Feb 2009 Posts: 20 Location: United States of america
|
|
|
|
Thanks for your reply.
I used COALESCE command and replaced null value with zeros |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Now i really am confused. . .
How did you use coalesce in Syncsort?
Maybe it will help others (and me) if you post completely/clearly what was going on and how both Syncsort and DB2 are involved. . . |
|
Back to top |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009 Posts: 2481 Location: Netherlands, Amstelveen
|
|
|
|
Hi Dick,
maybe a user exit in sort to trigger some DB2 processing?
Any thing is possible, seeing some requirements (?) and/or
inventions here. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hi Peter,
Quote: |
Any thing is possible |
Yup, it surely is.
I probably should have called a halt way back (like when i read the topic start . . . After a time, it appeared that there was no real continuity in the discussion and that Syncsort was mentioned (which does 0c7 on invalid numerics). Maybe i should have just left all of the clutter alone. . .
Hopefully, Sonali will clarify. . . |
|
Back to top |
|
|
sonali12_9
New User
Joined: 13 Feb 2009 Posts: 20 Location: United States of america
|
|
|
|
Sorry for the confusion.
Input for my sort was coming from db2 query. Query returned null values and i was not able to sum those values.
so later i used coalesce command and replaced null values with zeros .
Now i can sum the fields. |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
sonali12_9 wrote: |
Sorry for the confusion.
Input for my sort was coming from db2 query. Query returned null values and i was not able to sum those values.
so later i used coalesce command and replaced null values with zeros .
Now i can sum the fields. |
In real it made a confusion here, your post is moved to JCL Part while it belongs to DB2...try to be clear from the start of thread. That saves time for lots of people - please let us know where do you want to keep this thread now? |
|
Back to top |
|
|
|