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.

[Yousef_Amarneh]

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

[#3]

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

[m_woolf]

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

[#5]

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

[m_woolf]

Have you tried:

where text(AFF_AFFAIRE3) = '1312E5'

[#7]

I get 0 lines with this

[mrybalko]

Could you share your qvw with strange data?

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

[#9]

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 ?

[m_woolf]

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

The values on the left are considered to be numeric.