Why QV shows '001' instead of '0001'

[#1]

Hi,

I don't understand why QV in some cases (when a lot of rows is showed) shows standard character SQL fields by removing one 0 if there are two zeroes. But, when I try to see it and include only several records it shows correct values. It is plain situation where ona character field from a MS SQL table is parsed like this:

...

mid(TBFIELD1,15,10) as BlaBla,

...

select TBFIELD1, ... from ...;

I've also aligned entries as text, ie.e. to be left oriented.

So, in one case I see this:

123456   001   blablabla1 --> not correct!

123456   1234  blablabla2

and in another this:

123456   0001  blablabla1

123456   1234  blablabla2

It just does not make sense.

Thanks for ideas.

Best regards,

Nenad

[#2]

Hi all,

No one has an idea about this strange behaviour?

Thanks

[#3 · Accepted solution]

For now I've found this, but this is very, very strange. I mean converting text to text, etc.

load

...

text(MISC) as BlaBla,

...

select ..., substring(TBFIELD1,15,10) MISC, ... from ...;

Nenad

[rwunderlich]

For like numeric values, QV uses the first display representation seen by load order.

If loading:

01

0001

00001

1

The display will be "01" for all values.

If loading

1

00001

01

The display will be "1" for all values.

-Rob

[#5]

Hi Rob,

Well, it's not good enough for a real world. You could have all combinations available in a system (e.g. '001' and '0001' representing two different entities) and because we are talking about text fields it should segregate them instead of using the ackward logic and thinking too much. With the workaround I was able to present valid values to my users.

Thanks,

Nenad

[rwunderlich]

If you want to preserve all the various combinations for display, the solution, as you have discovered, is to use the text() function. I only meant to point out that for numeric values, this is "expected" QV behavior -- although it is usually surprising when first encountered.

-Rob