strange OLEDB select result

[#1]

Hi,

I isolated a bug in my application :

I load data from a MS sql 2008 DB

OLEDB CONNECT TO [...]

select AFF_AFFAIRE,AFF_AFFAIRE1,AFF_AFFAIRE3 from affaire;

now , for a selection that should give me one row, qlikview gives me two :

If I try the same thing in SQL management studio :

select AFF_AFFAIRE from AFFAIRE where AFF_AFFAIRE1='ITC' and AFF_AFFAIRE3='1312D5'

I get :

AFF_AFFAIRE

AITC592541312D500

which is the correct behavior.

What am i missing ?

Jo.

[mrybalko]

1312D5 value is number. I changed AFF_AFFAIRE3 format to number and got 131 200 000

I think 1312D5 value is interpreted as number: 1312 in power 5. What is the column data type of AFF_AFFAIRE3? Try to use another OLEDB driver or try ODBC connection.

[#3]

every field is a varchar(X)

using odbc doesn't change anything...

using '_'+AFF_AFFAIRE3 as AFF_AFFAIRE3 (using oledb) solves it but is ugly

any hint on how to force QV to load the field as text ?

[m_woolf]

Can you maybe load as is and then either do a preceding load or a resident load to do the convert to text?

[mrybalko]

Try the following code:

OLEDB CONNECT TO...

affaire:
LOAD
    AFF_AFFAIRE,
    AFF_AFFAIRE1,
    text(AFF_AFFAIRE3) as AFF_AFFAIRE3;
select
    AFF_AFFAIRE,
    AFF_AFFAIRE1,
    AFF_AFFAIRE3
from affaire
where AFF_AFFAIRE1='ITC' and AFF_AFFAIRE3 LIKE '1312%5';

[jonathandienst]

Hi

1312D5 will be treated as a number having a mantissa part 1312 and exponent part of 10^5 (D = double precision exponential number). This is numerically the same as..

1312E5 which is a number having mantissa part of 1312 and an exponent part of 10^5 (E = single precision exponential)

You need to load these as text using the Text() function to prevent Qlikview from treating them as numbers, like in the previous post from Maxim Rybalko. Qlikview looks at the data itself ro the types and does not consider the OLEDB and database types.

HTH

Jonathan

[#7]

thanks for the explanation, I didn't understood why xxxD5 was interpreted as a number.

[#8]

it does not work. Once the value is loaded as a number, I guess the bad row matching is done.

[mrybalko]

Thank you Jonathan. I did not know about double precision exponential number format

[#10]

thank you, this works, but I dont see how i can use this in my app:

i store snapshots of dozen of tables (mysql and ms sql) using select *

then i load them in my app using load.

adding the full definition of each table in the scripts would be awful to maintain...

[#11]

[edit] I now use this one : Re: strange OLEDB select result

for the record, here is the solution I chose for my problem :

temp:

SELECT * FROM AFFAIRE;

DROP FIELD AFF_AFFAIRE3; // incorrect numeric field

JOIN LOAD  AFF_AFFAIRE,Text(AFF_AFFAIRE3) as AFF_AFFAIRE3 ;

SELECT AFF_AFFAIRE,AFF_AFFAIRE3 FROM AFFAIRE;

STORE temp INTO snapshot.qvd (qvd);

DROP TABLE temp;

// later :

affaire:

LOAD AFF_AFFAIRE,

    AFF_AFFAIRE1,

    AFF_AFFAIRE3

FROM test2.qvd (qvd);

the only drawback is that I have to repeat the select twice.