Teradata Fastload issue

[shershahkhan]

I am trying to extract data from a teradata table with the defination

Code: Select all

CREATE SET TABLE decimalerror ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      Col1 DECIMAL(5,2))
PRIMARY INDEX ( Col1 );

The following data is then inserted

• 50.00
  124.00
  12.00
  167.09

When i try to retrieve the data in a server job through MLOAD stage(Select col1 from decimalerror ) and change the datatype of the column in datastage to Decimal(9,5), the following result is show in the output file

• 0.05000
  0.16709
  0.12400
  0.01200

The job doesn't gives any warning or error message that the datatypes are not correct. but seems like it is dividing by 1000. Can any body confirm if this is datastage issue or do i have to change some setting.

[vinothkumar]

Try using 'decimal_from_decimal' function for converting the values

[vinothkumar]

Try using 'decimal_from_decimal' function for converting the values

[ray.wurlod]

Not in a server job.

Multiply the retrieved values by 100. The values are stored with an implied decimal point. You could also use Fmt(), but multiplication is likely to be more efficient.

[shershahkhan]

Thanks guys for the solution but the problem is a bit different, its a migration project, i have changed the code to Teradata for more then 2000 jobs. now when i am testing the data i face this problem, i thought if there is some global setting that i can do which will fix the problem else i have to go through each and every job to see the types and find which job will give this problem. I think i will have to go through each and every job to find this problem

[chulett]

its a migration project, i have changed the code to Teradata for more then 2000 jobs.

You might want to expand a little bit on that... 'changed the code' from what? Didn't this come up in your testing before you did a wholesale change to 'more than 2000 jobs'?