Extremely strange behaviour when concatenating two tables - ID=001 and ID=0000001 become the same!!?

[ivan_will]

Hi all,

Could you please tell me what will happens when execute the following script:

LOAD * INLINE [

    F1, f2

    00000001, one

];

LOAD * INLINE [

    F1, f2

    001, two

];

it should be the table:

F1, f2

00000001, one

001, two

but it isn't!!!!!!!!!!!

it is

F1, f2

00000001, one

00000001, two

WHY????

it is strange , very strange! if I use text(F1) it will be represent as a text, but I do not want to use it, what to do?!

[tresesco]

ultimately 00000001 and 001 are same when they are treated as number. The QV considers the first numeric format for same number. That is if you put the second table first you will get

F1, f1

001, two

001, one

Regards,

tresesco

[ivan_will]

Ok, it is uncorrect from the QV, SQL makes it right.

Think it is a bug!!!

So how to escape the situation without putting everywhere text (ID) as ID ....

[swuehl]

Are you sure that your SQL server doesn't store the data as char or varchar and you're not calling the F1 data from two different table fields, possibly formated in different ways?

I am almost sure that you won't be able to create a table in SQL that stores the data in numerical format, but allows you to set a text representation differently for every given field value (like a SQL replacement for your QV concatenated table).

IMHO:

If the leading zeros are part of the value, they should be stored that way in the database/datamodel. (i.e. as text)

Regards,

Stefan