topic Re: 19-digit character in SQL db in QlikView

19-digit character in SQL db

gerhardl — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a 19-digit TRAN_ID with data type bigint in a SQL database. it might look like this: 2314885530818453536

When I load it into QlikView it ends up like this: 2.3148855308185e+018

I've had this before and got it to work by doing the below load method but this time it doesn't work and I don't understand why:

LOAD text(TRAN_ID) as TRAN_ID from XXX

SQL SELECT CAST(TRAN_ID as varchar) as TRAN_ID,*

I've also tried other options to varchar but it just refuses to work. What am I doing wrong?

Re: 19-digit character in SQL db

Anonymous — Wed, 10 Aug 2016 14:03:39 GMT

try this ?

LOAD NUM((TRAN_ID),'###################') as TRAN_ID from XXX

SQL SELECT CAST(TRAN_ID as varchar) as TRAN_ID,*

Re: 19-digit character in SQL db

gerhardl — Wed, 10 Aug 2016 14:07:15 GMT

Hi,

Doesn't work - everything then changes to 9223372036854780000

The last few digits are being dropped.

Re: 19-digit character in SQL db

marcus_sommer — Wed, 10 Aug 2016 14:14:34 GMT

AFAIK could qlikview not handle such big numbers else only until 14 digits. But to load them as text should work, maybe within a preceeding load on top your sql, like:

Load *, text(TRAN_ID) as TRAN_ID_TEXT;

SQL SELECT * From Source;

- Marcus

Re: 19-digit character in SQL db

maxgro — Wed, 10 Aug 2016 14:25:15 GMT

in the load, try to keep the TRAN_ID as a text and convert to char in the SQL part

LOAD

TRAN_ID,

...............

;

SQL

SELECT

CAST(TRAN_ID as varchar) as TRAN_ID,

FROM

.......................

Re: 19-digit character in SQL db

vishsaggi — Wed, 10 Aug 2016 14:25:35 GMT

Did you try this just wondering ?

LOAD *,

Num#(TRAN_ID) AS Tran_ID

.....;

Thanks,
V.

Re: 19-digit character in SQL db

gerhardl — Thu, 11 Aug 2016 05:13:52 GMT

Hi,

This is what I've tried already, as posted in my question originally.

tried with and without casting in SQL select statement

Re: 19-digit character in SQL db

gerhardl — Thu, 11 Aug 2016 05:14:35 GMT

Doesn't work.

Re: 19-digit character in SQL db

gerhardl — Thu, 11 Aug 2016 05:15:12 GMT

Yes, but doesn't work.

Re: 19-digit character in SQL db

marcus_sommer — Thu, 11 Aug 2016 05:25:09 GMT

Maybe not the most elegant approaches but two different workarounds which should work:

Load

text(keepchar(TRAN_ID_2, '0123456789')) as TRAN_ID_TEXT

text(TRAN_ID_PART_1 & TRAN_ID_PART_2) as TRAN_ID_TEXT_2;

SQL SELECT

'#' & TRAN_ID & '#' as TRAN_ID_2,

mid(TRAN_ID, 1, 9) as TRAN_ID_PART_1,

mid(TRAN_ID, 10) as TRAN_ID_PART_2

From Source;

- Marcus

Re: 19-digit character in SQL db

Thu, 11 Aug 2016 05:44:18 GMT

Hi Gerhard,

try this i am not sure,

=Text(Train ID) in dimension Expression

Thanks

Re: 19-digit character in SQL db

maxgro — Thu, 11 Aug 2016 07:53:32 GMT

I tried on a sql server db and it works, see the image with sql server (top) and qlik result (bottom)

LOAD text(TRAN_ID) as TRAN_ID;

SQL SELECT

cast(TRAN_ID as varchar) as TRAN_ID

FROM "C4PLATINUM_BENZINE".dbo."Table_1";

LOAD text(TRAN_ID) as TRAN_ID;

SQL SELECT

convert(varchar, TRAN_ID) as TRAN_ID

FROM "C4PLATINUM_BENZINE".dbo."Table_1";

Re: 19-digit character in SQL db

gerhardl — Thu, 11 Aug 2016 09:54:21 GMT

Doesn't work for me.

Re: 19-digit character in SQL db

gerhardl — Thu, 11 Aug 2016 09:57:58 GMT

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Argument data type bigint is invalid for argument 1 of substring function.

Re: 19-digit character in SQL db

gerhardl — Thu, 11 Aug 2016 10:06:34 GMT

Okay, I got this right by accident, and I still don't know WHY this fix works, but it does.

I had this in my SQL select statement, which then returned 2.3148855308185e+018:

SQL SELECT cast(TRAN_ID as varchar) as TRAN_ID, *

I then changed it to this, and then it works and returns 2314885530818453536

SQL SELECT *, cast(TRAN_ID as varchar) as TRAN_ID

So literally the only "fix" was to move the asterisk which selects all the other fields to the front of the CAST function. When the CAST function comes first it for some reason doesn't work properly.

Makes no sense (to me).

Re: 19-digit character in SQL db

maxgro — Thu, 11 Aug 2016 12:09:54 GMT

I think it doesn't because you use select *

This works for me (without select * but with other fields)

It seems the select * has a side effect on cast(....), I think because with select * you extract twice the TRAN_ID

LOAD text(TRAN_ID) as TRAN_ID, col1, col2;

SQL SELECT

col1, col2, cast(TRAN_ID as varchar) as TRAN_ID

FROM "Table_1" t;

and also this

SQL SELECT

cast(TRAN_ID as varchar) as TRAN_ID, col1, col2

FROM "C4PLATINUM_BENZINE".dbo."Table_1" t;