Archives of the TeradataForum

Message Posted: Mon, 09 Feb 2009 @ 20:19:57 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

An existing production Multiload is dropping records where the value coming in does not fit the table definition.

We can not change the way that the input is created. I was hoping that a case statement in the Multiload would enable me to set invalid values to null.

Input layout

     .FIELD    USAGE_YEAR_BEG_VAL    *    CHAR(4);

Output layout

     Begin_Usage_Dy    BYTEINT,
     Begin_Usage_Mo    BYTEINT,

I tried to evaluate the data and change it to nulls when there is bad data but adding the following to the Multiload did not change the results plus it converted records containing spaces to 0 and records with values such as ‘1_ ‘ were still dropped.

     , case when substr(:USAGE_YEAR_BEG_VAL, 3,2) between -128 and 127
             then substr(:USAGE_YEAR_BEG_VAL, 3,2)
             else null

        End

     , case when substr(:USAGE_YEAR_BEG_VAL, 1,2) between -128 and 127
             then substr(:USAGE_YEAR_BEG_VAL, 1,2)
             else null

        End

Is there anything I can do in the Multiload or am I going to have to change the table definitions to Char(2)?

Michele

	<Prev	Next>		<<First	<Prev	Next>	Last>>