How to remove extra characters from a nullable field during unload?

[maxdp]

Hi,
I'm trying to unload records from a table to a file using the Platinum batch utility. The problem I'm facing is that one of the fields is being returned with a couple of extra characters in front - this is a nullable field of 26 characters, a timestamp to be precise. Can anyone tell me how to unload the table without these extra characters appearing the field as the file size is being increased by 2. The select statement I'm using is as follows:

SELECT CHAR(COALESCE(ROW_ID, ' '), 15)
,CHAR(COALESCE(X_AX_ID, ' '), 13)
,CHAR(COALESCE(X_AX_MATCHCODE, ' '), 1)
,CHAR(COALESCE(LAST_NAME, ' '), 50)
,CHAR(COALESCE(FST_NAME, ' '), 50)
,CHAR(COALESCE(X_MID_NAME, ' '), 50)
,CHAR(COALESCE(X_GENERATION, ' '), 30)
,CHAR(COALESCE(PER_TITLE, ' '), 15)
,CHAR(COALESCE(PER_TITLE_SUFFIX, ' '), 15)
,CHAR(COALESCE(SOC_SECURITY_NUM, ' '), 11)
,CASE WHEN BIRTH_DT IS NULL THEN
CHAR(COALESCE(' ',' '), 26 ) ELSE
CAST(BIRTH_DT AS CHAR(26)) END AS BIRTH_DT
,CHAR(COALESCE(SEX_MF, ' '), 1)
,CHAR(COALESCE(MID_NAME, ' '), 11)
,CASE WHEN LAST_UPD IS NULL THEN
CHAR(COALESCE(' ',' '), 26 ) ELSE
CAST(LAST_UPD AS CHAR(26)) END AS LAST_UPD
,CHAR(COALESCE(X_LAST_UPD_BY, ' '), 15)
FROM S_CONTACT
WHERE (X_DELETED_FLG <> 'Y'
OR X_DELETED_FLG IS NULL)

Would appreciate early replies as I have to get this done today.

Thank you,
maxdp.
*****

 

[blom0344]

Are the null fields preceded by unwanted characters?
What characters are that?
What do you mean 'when file size is increased by 2'?

T. Blom
Information analyst
tbl@shimano-eu.com

 

[maxdp]

Hi T.Blom,
Sorry about the late reply, have been busy with other stuff and didn't get to read my mails till now. I'll try to explain with an example:
File layout in JCL unload
Field1 10
Field2 10
Field3 26(nullable)
Field4 14
Field5 5

The size of the file is supposed to be 65. Instead, the batch platinum unload creates a file of size 67, inserting, I think, a couple of extra characters (spaces???, i haven't found out what they are yet) at the beginning of timestamp field3. I've managed to get around it by treating the characters as filler for further processing, but that is unwanted/unnecessary processing. Have any ideas?

Maxdp.
******