Problem while the COALESCE function for the CHARACTER fields

harry · New User Joined: 27 Mar 2006 Posts: 31

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

DavidatK · Posted: Tue Jan 16, 2007 9:07 pm

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.

harry · New User Joined: 27 Mar 2006 Posts: 31

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.

harry · New User Joined: 27 Mar 2006 Posts: 31

Hi Dave,

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

Warm Regards
Harry

DavidatK · Posted: Thu Jan 18, 2007 8:18 pm

Harry,