Data type IN DB from Oracle database issue

[Xeu911]

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!

[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 · Accepted solution]

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.

[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!