Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Multiload SQL and Case statement
 
From:   Pinti, Michele A

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016