I am having issues performing data conversions specially with dates and timestamps while using fastload. My script is pasted below. All my rows are being imported and sent to error_1 table because of column CLR_DT with error 2665. My table is being is created before using the fastload utility. Any commentary is appreciated.
Destination Table DDL:
To which type of data conversions are you referring?
The FastLoad script is just loading data.
Teradata expects the Date/Time/Timestamp data to be in a very specific format.
If the incoming data does not match the format as described in the table creation, then the row will be placed in the error table.
You defined the column as: CLR_DT DATE FORMAT 'YY/MM/DD' NOT NULL
You also indicated the incoming data was in "delimited" (VARTEXT) format. Thus, the data in the data file must be in the format of YY/MM/DD.
I am exporting the data from the same table. Creating a new empy version of the same table and fastloading the data that was exported from the original table. I am working on a tool to create fastload scripts on the fly however I need to figure out these syntax errors along the way so I can account for them in my programming.
The data was exported using Fast Export 10,000 rows with a delimiter of '~'. So unless I am missing some FastExport options in SQLAssistant the data should be a perfect match since it originally came from the same system.
If I remove the (date, format 'yy/mm/dd') I get the same error and rows are loaded to the error table like they are now.
Any ideas to bypass this error?
Sorry to double post but I believe I side stepped your question. The data in the exported file is DATE with FORMAT 'YY/MM/DD'. The table has this same type and format. However, as I understand RECORD VARTEXT requires all columns to be 1 of 3 types. In this case VARCHAR is what I set CLR_DT to in the 'Define' statement. So I need to cast the text from VARCHAR to DATA as a result even though the exported data is in fact in the correct date format.
FastExport itself cannot export the data and write it out in delimited format. So, I guess you must be CASTing your SELECT statement.
Do you have to use SQLAssistant?
Have you tried TPT?
Can you provide me with a sample row from your data file?