IBM Mainframe Forum

Hello,

I have an unload that I created that needs to be emailed , but one of the date fields is nullable causing a problem.
The unload automatically added a position for the null character into position 36.

The problem is the output character is low-values and needs to be spaces.

How do you override that ?

Here is the output diplayed from BMC..

LOAD DATA INDDN SYSREC
EBCDIC CCSID(37,65534,65534)
INTO TABLE
ZA$$PGS.$TABLE1
(FILL1 POSITION(1:5) CHAR (5)
,MRKR POSITION(6:11) CHAR (6)
,FILL2 POSITION(12:16) CHAR (5)
,ID POSITION(17:20) DECIMAL ZONED
,FILL3 POSITION(21:25) CHAR (5)
,END_DT POSITION(26:35) DATE EXTERNAL(10)
NULLIF BMC_NULL1=X'6F'
, BMC_NULL1 POSITION(36) CHAR(1)
,FILL5 POSITION(37:45) CHAR (9)
,CNT POSITION(46:54) DECIMAL ZONED
,UNUSE POSITION(55:80) CHAR (26)
)


Here is the Unload..
UNLOAD
FORMAT STANDARD
UNLOADDN SYSREC
SHRLEVEL CHANGE
DIRECT AUTO
ZONEDDECOVP(F,F)
FILL YES
SELECT ' '
,C.MRKR_GRP_ID
,' '
,C.MRKR_ID
,' '
,D.MRKR_END_DT
,' '
,COUNT(*)
,' '
INTO
FILL1 CHARACTER (05)
,MRKR CHARACTER (06)
,FILL2 CHARACTER (05)
,ID DECIMAL ZONED (04,0)
,FILL3 CHARACTER (05)
,END_DT
,FILL5 CHARACTER (09)
,CNT DECIMAL ZONED (09,0)
,UNUSE CHAR (26)
FROM DB2ADM.AMKVW003_ACCT_MRKR C
,DB2ADM.AMKVW002_MRKR_ID D
WHERE C.MRKR_GRP_ID = D.MRKR_GRP_ID AND
C.MRKR_ID = D.MRKR_ID AND
C.MRKR_GRP_ID IN
(SELECT DISTINCT A.MRKR_GRP_ID
FROM DB2ADM.AMKVW002_MRKR_ID A
,DB2ADM.AMKVW001_MRKR_GRP B
WHERE
A.MRKR_GRP_ID = B.MRKR_GRP_ID AND
B.MRKR_GRP_OPRTNL_FL = 'Y' AND
B.MRKR_GRP_WEB_FL = 'Y' AND
(A.MRKR_END_DT BETWEEN (CURRENT_DATE) AND
(CURRENT_DATE + 7 DAYS) )
)
AND ACCT_MRKR_ACTV_FL = 'Y'
GROUP BY C.MRKR_GRP_ID, C.MRKR_ID, D.MRKR_END_DT
;

Please help,

Thanks,
Paul

try coalesce(end_dt,' ')

I don't understand.

Where do I put this command ?

I tried it in the 'select' area and 'into' area and getting an error.
What is the syntax ?

Hello,

Post what you tried and the error this caused.

COALESCE is part of the SELECT. Use what GuyC posted instead of only the column name.