topic Re: strange OLEDB select result in QlikView

strange OLEDB select result

Thu, 31 Jul 2014 09:21:04 GMT

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.

Re: strange OLEDB select result

Yousef_Amarneh — Thu, 31 Jul 2014 09:30:48 GMT

try Rtrim(Ltrim(AFF_AFFAIRE1))='ITC' and Rtrim(Ltrim(AFF_AFFAIRE3))='1312D5'

Re: strange OLEDB select result

Thu, 31 Jul 2014 09:43:37 GMT

I get the same result. which is correct. the other row should be linked to AFF_AFFAIRE3 = '1312E5' , not 1312D5 :

select AFF_AFFAIRE,AFF_AFFAIRE1,AFF_AFFAIRE3 from AFFAIRE where AFF_AFFAIRE = 'AITC590461312E500';

AFF_AFFAIRE	AFF_AFFAIRE1	AFF_AFFAIRE3
AITC590461312E500	ITC	1312E5

Re: strange OLEDB select result

m_woolf — Thu, 31 Jul 2014 11:58:38 GMT

It looks like QlikView is looking at the numeric part of the field for the match. You might use the text() function when you load AFF_AFFAIRE3

Re: strange OLEDB select result

Thu, 31 Jul 2014 13:00:57 GMT

sound plausible since the field is tagged with $numeric, $integer.

when I do :

load AFF_AFFAIRE,AFF_AFFAIRE1,text(AFF_AFFAIRE3) from file.qvd (qvd)

- or -

if I try to tag/untag the field before or after my SELECT statement

then the field is then correctly tagged $ascii,$text

but in both case, I get the same issue

thank you for your help

Re: strange OLEDB select result

m_woolf — Thu, 31 Jul 2014 13:10:24 GMT

Have you tried:

where text(AFF_AFFAIRE3) = '1312E5'

Re: strange OLEDB select result

Thu, 31 Jul 2014 13:36:39 GMT

I get 0 lines with this

Re: strange OLEDB select result

mrybalko — Thu, 31 Jul 2014 13:47:35 GMT

Could you share your qvw with strange data?

And do you use the same DB user in QV and SQL manager?

Re: Re: strange OLEDB select result

Thu, 31 Jul 2014 14:03:42 GMT

Yes I use the same DB.

here is a trimmed down version of the test file.

the 1312D5 value is aligned right while the other values are aligned left. what does that mean ?

Re: Re: strange OLEDB select result

m_woolf — Thu, 31 Jul 2014 14:08:24 GMT

The value on the right is considered to be a label.

The values on the left are considered to be numeric.

Re: strange OLEDB select result

mrybalko — Thu, 31 Jul 2014 14:12:35 GMT

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.

Re: Re: strange OLEDB select result

Thu, 31 Jul 2014 15:14:52 GMT

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 ?

Re: Re: strange OLEDB select result

m_woolf — Thu, 31 Jul 2014 15:18:39 GMT

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

Re: Re: Re: strange OLEDB select result

mrybalko — Fri, 01 Aug 2014 08:00:11 GMT

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';

Re: strange OLEDB select result

jonathandienst — Fri, 01 Aug 2014 08:20:00 GMT

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

Re: strange OLEDB select result

Fri, 01 Aug 2014 09:13:33 GMT

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

Re: strange OLEDB select result

Fri, 01 Aug 2014 09:20:20 GMT

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

Re: strange OLEDB select result

mrybalko — Fri, 01 Aug 2014 09:47:18 GMT

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

Re: strange OLEDB select result

Fri, 01 Aug 2014 09:48:37 GMT

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

Re: strange OLEDB select result

Fri, 01 Aug 2014 13:02:37 GMT

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