Data type IN DB from Oracle database issue

Hi everyone,

I'm currently facing an issue when connecting to an Oracle table. The datatype in the DB in number (15.2) and Alteryx seems unable to read it because it gives only null values (data type in Alteryx is set as Fixed decimal 17.2 by default in my case). On the screenshots below you can have a view of the database and what I receive in Alteryx.

I have the same issue using in DB or in Memory connections. My guess is that the comma separator is the issue and I found a workaround by replacing the "," by "." in the SQL query.

However I'd like to know if my assumption is correct or if there is a better way to solve this? Maybe a configuration window to check the datatypes within Alteryx?

Thanks in advance for your help!

Settings

Error Message

Best Practices

Help

In Database

Database Connection

In-Database

Accepted answers

apathetichell

Hey - can you share your query here? If you are using a replace as a workaround - are you using a CAST to cast as string - and then re-cast as fixed decimal? If you are not - is this a string in Oracle - which you are casting as fixed decimal in your query - or is this a fixed decimal in Oracle? I'd like to get to the bottom of why is happening - I usually see Alteryx keep native data types.

All comments

apathetichell

under localization what is your setting for decimal delimiter? My hunch would be there is a conflict between what your DB sees as a number and what your system sees as a number. I haven't checked if this is customizable at an ODBC level - but Alteryx customizes sql with localization.

I noticed though that you said that the results are the same whether in-db or in memory - if you attach a browse in-db you're seeing this issue?

Xeu911

Hi @apathetichell currently I have this configuration :

I've tried different options but I'm still getting null values. And yes I'm seeing the same issue when using a browse in-db.

apathetichell

what is your query? This is fairly odd - so without knowing if there is a CAST or other manual conversion at some point - assuming that's not going on - I'd recommend recreating your in-db connection in manage in-db and trying again. If that doesn't work- open a support ticket.

apathetichell

Xeu911

Hi @apathetichell , the SQL query looked like this at first: select MONTANT_HTR, MONTANT_TTC from TMP_TEST_ARCD_FDG_IMPUT_FCT
We did use the replace function and cast the value as fixed decimal, it worked! However I wonder why it was not working and if there is a better option than using the replace function.

Thanks!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703