19-digit character in SQL db

[gerhardl]

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?

[#2]

Hi Gerhard,

try this i am not sure,

=Text(Train ID) in dimension Expression

Thanks

[maxgro]

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

T:

LOAD text(TRAN_ID) as TRAN_ID;

SQL SELECT

cast(TRAN_ID as varchar) as TRAN_ID

FROM "C4PLATINUM_BENZINE".dbo."Table_1";

T:

LOAD text(TRAN_ID) as TRAN_ID;

SQL SELECT

convert(varchar, TRAN_ID) as TRAN_ID

FROM "C4PLATINUM_BENZINE".dbo."Table_1";

[gerhardl]

Doesn't work for me.

[gerhardl]

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

[gerhardl · Accepted solution]

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).

[maxgro]

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 

T:

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;