Defaulting during Teradata table loading through fast load

Database

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.

2 REPLIES
Teradata Employee

Re: Defaulting during Teradata table loading through fast load

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.

Re: Defaulting during Teradata table loading through fast load

Thanks Fred .... :)