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
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.
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.