Teradata Forums / Teradata / Teradata Tools and Utilities / Loading space into a Decimal(15,2) column / Latest Posts

RE: Loading space into a Decimal(15,2) column

Fred Pluebell — Tue, 23 Jan 2007 12:15:26 GMT

You can't use NULLIF function in SQL because that won't be evaluated until after the bad data has been sent to Teradata. Did you try using MLOAD NULLIF processing on the client side, something like this?

.FIELD MyFldC 99 CHAR(8);
.FIELD MyFldPD 99 DECIMAL(15,2) NULLIF MyFldC = ' ';
...
INSERT ... VALUES(... :MyFldPD ...);

RE: Loading space into a Decimal(15,2) column

dwhraghav — Tue, 23 Jan 2007 09:07:32 GMT

If I am correctly understanding the problem, then I think you should try using TRIM Leading or Trailing or both

RE: Loading space into a Decimal(15,2) column

dmac — Thu, 14 Sep 2006 09:25:08 GMT

If all spaces is the only invalid value???? I would think you could define the the input area as both char and decimal. Then use a case statement to check for spaces in the char name and set the target to null or zero if space and otherwise to the decimal column name.

RE: Loading space into a Decimal(15,2) column

Anand JP — Thu, 07 Sep 2006 00:18:20 GMT

Thanks Feroz,

We already tried using CAST also we tried using NULLIF to replace spaces with NULL. The same behaviour is seen

RE: Loading space into a Decimal(15,2) column

feroz_shaik — Tue, 05 Sep 2006 12:21:53 GMT

Anand,
Could you add a case statement in the Mload to check for space in the source file and the load only the valid decimal values.

Loading space into a Decimal(15,2) column

Anand JP — Tue, 05 Sep 2006 02:05:19 GMT

Hi,

We are facing issues in loading a text file from a Mainframe system to Teradata database using Mulitload. Here are the details.

Problem Description :
In the text file that we are loading most of the records has "spaces" instead of packed decimal values for the column/field for which the error is being reported. Mulitload loads the records having columns with proper packed decimal values but rejects records which has spaces with error code 2679.

File Format : Text
Source Field datatype : Packed decimal (cobol comp3)
Target TD Column datatype : Decimal(15,2)
Error Message : 2679 The format or data contains a bad character

Has anyone encountered this problem before.

Please let me know your comments/suggestions.

With Regards,
Anand