Reading H2 zero's scientific notation

[hroderick-thr]

Hello

I'm using Alteryx to copy tables from Connect's H2 database to another database.

When reading the table STC_XFORMS.AR_TRANSITION the input tool sends many of this warning:

"ConvError: Input Data (31): rating: "0E-10" is not a valid FixedDecimal. FixedDecimal values must be of the form: -nnn.nn"

This is coming from using select * from STC_XFORMS.AR_TRANSITION in an input tool sql.   

The row is being written with null in the column instead of zero. The input tool is expecting decimal values on visual query builder tab.

Let me know if this would be better in the designer forum.

[carlosteixeira · Accepted solution]

Hi @hroderick-thr 

You will need to convert to STRING, TOSTRING(YOUR COLUMN).

After this in the Designer you can switch again to FIXED DECIMAL.

Take a look in this link:

https://community.alteryx.com/t5/Forum-Francais/Tips-de-la-semaine-52-Mieux-comprendre-le-format-Fix...

I hope this help you

CarlosTeixeira

[hroderick-thr]

Thanks Carlos.

I'm sure you solution will work but I will have to kluge my workflow to use it.

The use case is to migrate all tables so I'm hoping to use select * instead of specifying column names

This is running in a macro that I pass table names to.

I suppose I could build sql for each table that would select string(col1) as col1, string(col2) as col2, … from table but was hoping for a shortcut.

I didn't think there was a way to override the input tool's defaults or make it do a conversion, but wanted to ask the community before being certain.

Your solution may be the only solution today, so I will mark it