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
 

 

Syncsort - how to deal with null values. please help

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
sonali12_9

New User


Joined: 13 Feb 2009
Posts: 20
Location: United States of america

PostPosted: Thu Dec 10, 2009 11:51 am    Post subject: Syncsort - how to deal with null values. please help
Reply with quote

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
View user's profile Send private message

PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2422
Location: Netherlands, Amstelveen

PostPosted: Fri Dec 11, 2009 2:06 pm    Post subject:
Reply with quote

Gentlemen, the TS is using SYNCSORT as far as i could see.
Back to top
View user's profile Send private message
abhishek.sun

New User


Joined: 22 Aug 2008
Posts: 2
Location: Gurgaon

PostPosted: Fri Dec 11, 2009 2:11 pm    Post subject: Re: Reply to: how to deal with null values. please help
Reply with quote

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
http://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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Dec 11, 2009 9:42 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sonali12_9

New User


Joined: 13 Feb 2009
Posts: 20
Location: United States of america

PostPosted: Sat Dec 12, 2009 11:00 am    Post subject: Reply to: Syncsort - how to deal with null values. please he
Reply with quote

Thanks for your reply.
I used COALESCE command and replaced null value with zeros
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Dec 13, 2009 2:55 am    Post subject:
Reply with quote

Hello,

Now i really am confused. . . icon_confused.gif

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
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2422
Location: Netherlands, Amstelveen

PostPosted: Sun Dec 13, 2009 7:00 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Dec 14, 2009 3:35 am    Post subject:
Reply with quote

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
View user's profile Send private message
sonali12_9

New User


Joined: 13 Feb 2009
Posts: 20
Location: United States of america

PostPosted: Mon Dec 14, 2009 11:21 am    Post subject: Reply to: Syncsort - how to deal with null values. please he
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Mon Dec 14, 2009 3:02 pm    Post subject: Re: Reply to: Syncsort - how to deal with null values. pleas
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SYNCSORT SEQNUM and FI fields. nartcr SYNCSORT 5 Sat Jan 21, 2017 4:02 am
No new posts Syncsort Help to group fields sudhakarraju SYNCSORT 6 Thu Dec 29, 2016 1:38 am
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts syncsort: copy lines after the keyword shreya19 SYNCSORT 7 Fri Dec 02, 2016 9:47 am
No new posts Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am


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