topic Re: Oracle connection using ODBC - numbers converted to scientific notation in Connectivity & Data Prep

Oracle connection using ODBC - numbers converted to scientific notation

bartoszmusial — Tue, 05 Mar 2024 12:49:42 GMT

Hi, I have a problem with numbers automatically converted to scientific notation. I know that Qlik Sense has a limit of 15 digits. My problem is:

14 digit number is displayed properly

15 digit number is converted to scientific notation

number with decimal places is also converted to scientific (instead of 0.000007 we have 7e-06)

Is it a bug with ODBC connector? (our version is Version: 7.34.0)

OLE DB connector works as expected and displays all those numbers in regular number format, but it's 3 times slower in our case. NLS_LANG for OLE DB driver is set as POLISH_POLAND.AL32UTF8 (commas separates decimal digits)

Re: Oracle connection using ODBC - numbers converted to scientific notation

JonnyPoole — Tue, 05 Mar 2024 14:39:00 GMT

In this example, I can load 15 digits but the first renders in scientific notation, the 2nd renders in number format. See if you can solve your problem using the 2nd expression in a preceding LOAD statement

LOAD
Evaluate(Num1) as Num1,
Num(evaluate(Num2),'###,###,###,###,##0') as Num2

INLINE [
Num1,Num2
123456789012345,123456789012345];

Re: Oracle connection using ODBC - numbers converted to scientific notation

bartoszmusial — Sun, 21 Apr 2024 14:46:47 GMT

I run your sample code with additional digit:

LOAD
Evaluate(Num1) as Num1,
Num(evaluate(Num2),'##############0') as Num2

INLINE [
Num1,Num2
1234567890123456,1234567890123456];

Effect is:

Please check what effect do you have when you run:

load *;
sql SELECT
12345678901234 digits_14
,123456789012345 digits_15
,0.000007 digits_decimal_6
FROM
dual;

I have:

When i run:

load
Evaluate(DIGITS_14) as DIGITS_14,
Evaluate(DIGITS_15) as DIGITS_15,
Evaluate(DIGITS_DECIMAL_6) as DIGITS_DECIMAL_6
;
sql SELECT
12345678901234 DIGITS_14
,123456789012345 DIGITS_15
,0.000007 DIGITS_DECIMAL_6
FROM
dual;

Effect is:

Re: Oracle connection using ODBC - numbers converted to scientific notation

marcus_sommer — Mon, 22 Apr 2024 09:41:21 GMT

A few hints to the limitation of 15 digits. It relates to all and not only the decimal ones - means 0.000007 are 7 digits and not 6.

Further 15 digits could be stored as a numerical value but not or only restricted processed as a number. In my experience you could use them in mapping/joining or within an exists() but calculating/formatting them will mostly lead to NULL.

You may avoid the num() and evaluate() approaches by pre-loading your wanted interpretation, like:

Dummy: load 123456789012345 as MyFieldName autogenerate 1;

sql select MyFieldName from X;

drop tables Dummy;