On mainframe I have a DASD file which contains data. One of the fields I want to retrieve is a numeric field that contains percentage. The length of the field is 5 bytes. I defined the field as char(5) and tried tt FASTLOAD to a column in teradata as DECIMAL(5,2). I was hoping 1.78 (defined as 00178 in the input file) will be properly formated. When I browsed the data in the table, it appears as 178.00.
I will appreciate your help if you can tell me that instead of declaring the column as char(5), decimal(5) is valiud or further more Decemal(5,2) is valid. If not what is the way of doing this.
I think that's the expected output. You will have to store it as 001.78 in the file and use CHAR(6) to get the results your are looking for.
If you can't afford to change the input data file, then I think your options are limited to using a Inmod+fastload or multiload+div by 100 as fast load don't do much help on it's own when it comes to conversions.
Thanks for your responses. I will try to use the FORMAT command. The way I handled the situation that also worked is to load the data into staging table with input definition as x(5) and staging table layout as decimal(5,2). The data value 00135 was stored as 135.00. Ofcourse this was not a desired result. It should have been 1.35 instead. When I populated data from staging table into base table, i divided the column by 100 to get the desired results. Your approach of using the (format 999v99 looks a better approach.
Simple formating worked. I defined the column as x(5)and used the format options code is as follows: DEFINE SPRFL_BIRTH_DATE (CHAR(7)), SPRFL_PROFILE_ID (CHAR(10)), SPRFL_PROFILE_TYPE (CHAR(1)), SPRFL_PROFILE_NAME (CHAR(20)), SPRFL_PERIODICITY (CHAR(3)), SPRFL_MESSAGE_FLAG (CHAR(1)), SPRFL_USE_DATE (CHAR(7)), SPRFL_INDX01 (CHAR(5)), SPRFL_INDX02 (CHAR(5)), SPRFL_INDX03 (CHAR(5)),