Leading Zeroes in Btree (AREV Specific)
At 30 MAY 2004 05:38:39PM ps wing wrote:
Hello,
I have a problem where a btree has been added to a key part field which is 14 char long and has upto 7 leading zeroes.
The btree does not return all records whereas @id "xxxx" does.
I can not find an answer by doing a search, so any ideas?
Thanks.
Phil
At 31 MAY 2004 05:10AM support@sprezzatura.com wrote:
The leading zeros are removed from the index - see http://www.revelation.com/__85256DB80017688B.nsf/0/361409AD3C566B1985256A950051214E?OpenDocument
support@sprezzatura.com
The Sprezzatura Group Web Site
World Leaders in all things RevSoft
At 31 MAY 2004 03:31PM Curt Putnam wrote:
From a practical standpoint, a re-think of the ID is due. Regardless, you'll have to do without the leading zeros. If necessary, prepend some character(s).
At 31 MAY 2004 11:36PM ps wing wrote:
Seems I dont even get consistent results when doing a select when @id containing 'xxxx' where the first key part has leading zeroes.
I have to run the select twice to get them all. Very strange, never mind.
At 31 MAY 2004 11:37PM ps wing wrote:
Seems too that this discussion thread is not updating, oh well.
At 31 MAY 2004 11:46PM Richard Bright wrote:
I think that you were given the answer - that is BTREE strips the leading zeros out. So if you want to preserve the padding of leading zeros, add IN FRONT a character, number, whatever.
At 31 MAY 2004 11:58PM ps wing wrote:
But Im not using the btree any more just @id containing whatever.
At 01 JUN 2004 04:21PM Richard Bright wrote:
OK,
Would seem that the key is being recognised as a numeric - thus triming the lead '0000's. Remember 0000123 == 123 as numeric but as Alpha string get different result. So you may wish to check the Dict for the key and if integer, change to varchar.
At 01 JUN 2004 11:31PM ps wing wrote:
Im not using leading zeroes on key part one now as it also caused pretend GFEs.
Key part one is alpha numberic and defined as left CHAR(14)and is unique regardless of the leading zeroes.
Without the leading zeroes I dont get any phantom GFEs and a select works with or without the btree.
Still strange, but I will put it behind me.