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
 

 

Problem while the COALESCE function for the CHARACTER fields

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
harry

New User


Joined: 27 Mar 2006
Posts: 31

PostPosted: Tue Jan 16, 2007 3:39 pm    Post subject: Problem while the COALESCE function for the CHARACTER fields
Reply with quote

Hi,

I am executing the following unload jobs and every time i am facing some problem ,can any one help me out from finding the reason for following conditions:

CASE 1:

COALESCE is not working for any character fields.I want to know why this is happening?

CASE 2:

If i will unload through the DB-2 utility DB2CPROC and if i won't give any COALESCE condition for the null filelds the unload will happen proper, the only thing which we have to do in the copybook is just give one indicator field just after the null field.Then you will get the proper output.

CASE 3:

COALESCE is working very fine for the STANDARD DATA TYPES.

CASE 4:

For any character fields we can't execute the following query through unload jobs:

COALESCE(PRD_CLASS,'ABCDEF(because length is 6 bytes)'),means if null comes for the PRD_CLASS put forcefully ABCDEF into the output file.

If any one had face any problem please help me to find out the solution for the mentioned problem.

Regards
Harry
Back to top
View user's profile Send private message

DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Tue Jan 16, 2007 9:07 pm    Post subject: Re: Problem while the COALESCE function for the CHARACTER fi
Reply with quote

Harry,

What happens when you try to use COALESCE with Character data type? Does it give SQL error?

I use COALESCE on Char columns in unloads often.
Back to top
View user's profile Send private message
harry

New User


Joined: 27 Mar 2006
Posts: 31

PostPosted: Wed Jan 17, 2007 12:28 pm    Post subject:
Reply with quote

Thanks for your reply!!

Dave,

Unload happened proper but data after the COALESE was complety wrong.I would like to mention over here that i was using the utility DB2CPROC,the same if some one will do by cobol-db2 program then it will come perfect.
Back to top
View user's profile Send private message
harry

New User


Joined: 27 Mar 2006
Posts: 31

PostPosted: Thu Jan 18, 2007 12:00 pm    Post subject:
Reply with quote

Hi Dave,

Could you please let me know that how you used COALESCE for the character fields.

Warm Regards
Harry
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Thu Jan 18, 2007 8:18 pm    Post subject: Re: Problem while the COALESCE function for the CHARACTER fi
Reply with quote

Harry,

Code:

  COALESCE(CHAR_COLUMN,? ?)


When you say everything after the COALESCE was completely wrong, how was it wrong? Please post the Unload SQL before and after, including the COALESCE, and the output from it. Thanks

I think ?DB2CPROC? is probably an in-house procedure?
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 How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am
No new posts What is the Extract Function in JCL dharmaraok JCL & VSAM 3 Wed Jun 14, 2017 9:12 am
No new posts Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts Sum Fields ballaswaroop DFSORT/ICETOOL 4 Tue May 02, 2017 11:07 am
No new posts Strings with double quotes having pro... raja Arumugam All Other Mainframe Topics 11 Thu Mar 30, 2017 10:34 am


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