All Forums Database
pradip_rang 2 posts Joined 07/16
21 Jul 2016
Defaulting during Teradata table loading through fast load

Hello everyone,
I am new to database and table loading, I am trying to load pipe separated .txt file into teradata table and i am using fast load mechanism.
My requirement is that for few columns I have write defaultung logic so that if null value found in the file then i need to use the default value may be -1 or so...
Let's assume I have below data to be loaded in the table

1234|ABCD|20|1234567||D1|41275
1235|ABCE||||D1|
1236|ABCF|12|1234568||D1|41275
1237|ABCG||||D1|41275
1238|ABCH||1234569||D1|
1239|ABCI|1|||D1|41275

I want output like this:
If null value in the Mobile_Number then insert default value -1
Company name has to be harcoded to CCC
Joining_Date format should be dd-mm-yyyy and if any null value then put default date to 01-01-99.
 
Table should have data like below:

Emp ID  NAME    Age Mobile_number Company_Name Department   Joining_date
1234    ABCD    20        1234567          CCC        D1        1-jan-13
1235    ABCE                   -1          CCC        D1        1-jan-99
1236    ABCF    12        1234568          CCC        D1        1-jan-13
1237    ABCG                   -1          CCC        D1        1-jan-13
1238    ABCH              1234569          CCC        D1        1-jan-99
1239    ABCI    1              -1          CCC        D1        1-jan-13

Can any please help with his, do let me know if you need any more information from me.
 
 
 
 

Fred 1096 posts Joined 08/04
22 Jul 2016

FastLoad supports changing default values from the source to NULL, but not the other way around.
 
MultiLoad / TPT UPDATE would allow you to specify a COALESCE or CASE expression as part of the INSERT statement. Or you can FastLoad the NULL values to an interim staging table, then INSERT/SELECT the data to the final table with COALESCE / CASE.

pradip_rang 2 posts Joined 07/16
24 Jul 2016

Thanks Fred .... :)

You must sign in to leave a comment.