community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Specified column precision is greater than the maximum precision

Asteroid

Hi,

 

I've created an Alteryx workflow to extract and consolidate data from a SQL Server database (via in-db), and then write the output to a different database using the "output data" tool.

 

However, I get an error message at the last step (output data): "Specified column precision 39 is greater than the maximum precision of 38". However, I don't remember setting any column precision so i'm not too sure why I'm getting this error message.

 

Does anyone have any idea how this can be fixed?

 

Thanks

 

 

afk

Alteryx Partner
Alteryx Partner

I would recommend you to check the metadata on your workflow or look at the Workflow using the browse tool.

 

It seems that at some point on your calculations the precision of your data was increased (maybe required due the calculations performed on it).

 

As the error states, the maximum precision on the SQL server is now 38 digits, therefore I would recommend you to either round or specify a shorter precision for your column.

 

SQL Server precision: https://docs.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=s...

 

Cheers

Asteroid

Hi @afv2688 

 

Thanks for your response.

 

I can see that the size of the calculated column in question is (40,4). Can you advise on how to round these figures or specify shorter precision?

 

 

afk

Alteryx Partner
Alteryx Partner

Hi @akasubi ,

 

You could use the formula tool to create a new field with 'FixedDecimal' Data type and change the value of 'Scale' (which sets the precision of your data).

 

Add in there the field and after deselect the old field and rename the new one.

 

Cheers

Asteroid

Hi @afv2688 

 

I'm using the Formula In-DB tool, is it possible to change the value of 'Scale' here as I can't seem to see it. The column type is already FixedDecimal (with 40,2 size) so I tried to convert/cast this column and specify the length of the decimal but it doesn't seem to work. Am I doing it the right way?

 

Sorry for all the questions.

 

 

afk

Alteryx Partner
Alteryx Partner

In the formula tool as you tried the cast function, did you tried to cast 'as double'?

 

Also, don't try to overwrite the field applying a new cell format, it won't work. Do it on a new one

 

It should work that way.

 

P.D: Np about the questions, thats what this is for :)

 

Cheers

Asteroid

Hi @afv2688 

 

So using the formula in-db tool, I've created a new output field and have used convert and cast to change the precision of the decimal. I've tried both double and fixedDecimal types for the output field as well.

 

convert(decimal(2,4), "SumColumn1")

and

cast("SumColumn1" as decimal(2,4)

 

But when I check the metadata, the new field is still coming out as fixedDecimal with size 40,4. Did I write the formula incorrectly? Alteryx seems to have increased the precision when I summarize Column1 because the size before the sum is 20,4.

Asteroid

Hi @afv2688 

 

I seem to have resolved the issue by defining the new output field with the same parameters as the fixedDecimal columns before they were summarized. 

 

cast("SumColumn1" as decimal(20,4)

 

I've been able to write the output back into SQL Server.

 

Thanks for your help!

Labels