Does use of DB2 function in UNLOAD return indicators or something? When I do straight unload I get good data. When I add ABS or CASE data after that function column is pushed over 1 byte. Is there an option I need to use to ensure this doesnt happen?
Code:
UNLOAD
FIXEDVARCHAR YES
DIRECT NO
SELECT TAB1.PYR_ID
,TAB2.YR_DT
,TAB3.ACCT_CD
,TAB3.ACCT
,TAB3.TRAN_QTY
,TAB3.TRAN_AMT
,TAB3.TRAN_ID
When I do CASE the unload it looks like a get all ZEROS in the QTY field
Code:
UNLOAD
FIXEDVARCHAR YES
DIRECT NO
SELECT TAB1.PYR_ID
,TAB2.YR_DT
,TAB3.ACCT_CD
,TAB3.ACCT
,DEC(CASE
WHEN TAB3.TRAN_QTY < +0
THEN TAB3.TRAN_QTY * -1
WHEN TAB3.TRAN_QTY > +0
THEN TAB3.TRAN_QTY
END,17,4)
,TAB3.TRAN_AMT
,TAB3.TRAN_ID
The result of the function has the same data type and length attribute as the argument. The result can be null. If the argument is null, the result is the null value.
Utility guide and reference:Figure 103. Layout of a nullable fixed-length field wrote:
The following figure shows the layout of a fixed-length field that can be null. This diagram shows that a null indicator byte is stored before the data field, which begins at the specified position or at the next byte position past the end of the previous data field.
And if you would have looked at SYSPUNCH (possibly set to dummy in your JCL) you could have guessed this.
try: cast(coalesce(abs(TAB3.TRAN_QTY),0) as dec(17,4))