Decimal or VARCHAR Null handling

Database
Enthusiast

Decimal or VARCHAR Null handling

I need help in Null handling, here are the details:

I am extracting data from one Teradta system using Fast export, copy data to a mainframe dataset, and want to Load to another Teradata system.

There are few different data types CHAR,VARCHAR, DECIMAL(13,2) fields, and to account for Nulls, i use following in extract:

/* High values are substituted for Nulls. */

SELECT
A.FIELD1
,A.FIELD2
,COALESCE(A.FIELD3,X'FF') (CHAR(06))
,A.FIELD4
,COALESCE(A.FIELD5,X'FF') (CHAR(16))
,CAST((CASE
WHEN A.FIELD6 IS NULL
THEN X'FF'
ELSE ' '
END) AS CHAR(01))
,COALESCE(A.FIELD6, 99999999999.99)
FROM
DB.TableName

Once Records are extracted, i used following Layout to read it:

.LAYOUT DATAIN_LAYOUT;
.FIELD FIELD1 001 BYTE(8);
.FIELD FIELD2 009 BYTEINT;
.FIELD FIELD3_NI 010 CHAR(001);
.FIELD FIELD3 010 SMALLINT
NULLIF FIELD3_NI ='FF'XB ;
.FIELD FIELD4 012 SMALLINT;
.FIELD FIELD5_NI 014 CHAR(001);
.FIELD FIELD5 014 SMALLINT
NULLIF FIELD5_NI ='FF'XB ;
.FIELD FIELD6_NI 016 CHAR(001);
.FIELD FIELD6 017 DECIMAL(13,2)
NULLIF FIELD6_NI ='FF'XB ;

but last colum goes to Error table with Error code 2679.

Question is: how do i handle DECIMALs and VARCHARs when there is a Null in the source table. If i dont handle them, then when I Export to a dataset, it saves it as a SPACE, and then gets loaded to table as SPACE instead of Nulls.

Any helps is appreciated.

2 REPLIES
Senior Apprentice

Re: Decimal or VARCHAR Null handling

When you export "COALESCE(A.FIELD3,X'FF') (CHAR(06))" you can't use ".FIELD FIELD3 010 SMALLINT" for import, because the data types don't match.

But why don't you use the built-in option to deal with NULLs?

FExp: .EXPORT ... MODE INDICATOR;
MLoad: .LAYOUT ... INDICATORS;

Dieter
Enthusiast

Re: Decimal or VARCHAR Null handling

Dieter, thank you for sharing it. I am glad to know I did not have to go thru hell to handle Nulls. That sure changes the way we do things at work... Love Teradata....:)