ODBC Redshift

[Rally00]

Hi everyone
I have a problem with an ODBC connection from Redshift.
Once connected, for some tables, it is not possible to load them in direct query due to a conflict between the format of some
table columns and the data they contain
In particular fields that have the format INT and have "" values.

Below is the error:

DataSource.Error: ODBC: ERROR [22P02] [Microsoft][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 22P02] ERROR: invalid input syntax for integer: ""

Dettagli:
DataSourceKind=AmazonRedshift
DataSourcePath=loit-it0406172-prod-rshift-01fe-workgroup.925654835947.eu-central-1.redshift-serverless.amazonaws.com;dre2bi1prod
OdbcErrors=[Table]

Can you help me?
Thank you
Domenico

[Anonymous · Accepted solution]

Hi @Rally00 ,

 It appears that the issue arises because Redshift expects an integer value, but an empty string is provided instead. This mismatch in data types causes the error.

If possible, consider cleansing your data before it's queried. This could involve updating the source data to ensure that all fields expected to be integers do not contain empty strings or non-numeric values. This approach requires access to the source data and the ability to modify it.

Once you make selections from the Navigator window, you can either Load or Edit the data.

• If you choose to Load data, you'll be prompted to use either Import or DirectQuery mode to load the data. For more information, check out this article that explains DirectQuery.
• If you select to Edit the data, Query Editor appears where you can apply all sorts of transformations and filters to the data, many of which are applied to the underlying Amazon Redshift database itself (if supported).

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.