Export - JDBC error - formula to ensure a column has a right length

Hi

I am loading 1.9 million records back to database and l will often receive this message (part of)

 An example of an invalid value is data of numeric type with scale greater than precision.\

When l do a profile on the file prior to loading to look of longest length, it states that the field is no longer than required in database. When l download into csv, l can see l have fields that have a bigger length.

Therefore, prior to exporting l would like to format the columns to only be a certain length (ok at this point if dropped off at end). If column should be 10 characters, l would like to put in the few columns that cause me issues the following:

If length is equal or less than 10 then the field if false l would like to shorten the record to 20

not sure how to get the length to fit the length

Eg. Source id

123456789 (9 characters) - it will take the 9 char

12345678910 (11 characters) - it will shorten the field content to 1234567891

Can you please provide a formula to do the fixed length. I will clean the data up to fix the data cleansing in another step down track. First to get it in and then work out whats causing it

thanks

Answers

  • Sueann SeeSueann See Administrator

    @Carolyn Regarding your comment "When l do a profile on the file prior to loading to look of longest length, it states that the field is no longer than required in database. When l download into csv, l can see l have fields that have a bigger length." , do you have a specific example you can share so we can confirm there isn't an issue with the profiling first?

  • Ian BuckleIan Buckle Administrator

    Hi Carolyn,


    You can use the sub-string function to achieve the trimming of the field


Sign In or Register to comment.