All Forums Database
zaino22 5 posts Joined 10/10
07 Oct 2010
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.

dnoeth 4628 posts Joined 11/04
07 Oct 2010

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

Dieter

zaino22 5 posts Joined 10/10
08 Oct 2010

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....:)

You must sign in to leave a comment.