Unique index problem
Unique index problem
(OP)
I have a weird problem in dBase III+
DB structure:
Field Field Name Type Width Dec
1 JNUMMER Numeric 6
2 BRIN Character 4
3 PLDATUM Date 8
4 LEERJAAR Numeric 1
5 TICK Logical 1
(set date british
set century on
set deleted on)
. list all for jnummer=23
Record# JNUMMER BRIN PLDATUM LEERJAAR TICK
738 23 20TQ 1986.12.08 8 .T.
14213 23 17YS 1995.08.01 2 .T.
19066 23 17YS 1998.08.01 0 .F.
. INDE ON STR(JNUMMER)+BRIN+DTOC(PLDATUM) TO BRINHIS UNIQUE
(or SET UNIQUE ON and then INDEX without the UNIQUE)
. list all for jnummer=23
Record# JNUMMER BRIN PLDATUM LEERJAAR TICK
14213 23 17YS 1995.08.01 2 .T.
738 23 20TQ 1986.12.08 8 .T.
This is wrong, the index includes the date, so all three records are unique. The 19066 record should be there.
Other symptoms:
I cannot BROWSE, I constantly get 'Record is not in index', whether I GO BOTTOM or GO TOP or SKIP first
What can be going on here?
Thanks in advance
Jan
DB structure:
Field Field Name Type Width Dec
1 JNUMMER Numeric 6
2 BRIN Character 4
3 PLDATUM Date 8
4 LEERJAAR Numeric 1
5 TICK Logical 1
(set date british
set century on
set deleted on)
. list all for jnummer=23
Record# JNUMMER BRIN PLDATUM LEERJAAR TICK
738 23 20TQ 1986.12.08 8 .T.
14213 23 17YS 1995.08.01 2 .T.
19066 23 17YS 1998.08.01 0 .F.
. INDE ON STR(JNUMMER)+BRIN+DTOC(PLDATUM) TO BRINHIS UNIQUE
(or SET UNIQUE ON and then INDEX without the UNIQUE)
. list all for jnummer=23
Record# JNUMMER BRIN PLDATUM LEERJAAR TICK
14213 23 17YS 1995.08.01 2 .T.
738 23 20TQ 1986.12.08 8 .T.
This is wrong, the index includes the date, so all three records are unique. The 19066 record should be there.
Other symptoms:
I cannot BROWSE, I constantly get 'Record is not in index', whether I GO BOTTOM or GO TOP or SKIP first
What can be going on here?
Thanks in advance
Jan
Talk To Other Members
RE: Unique index problem
The error was in the STR(JNUMMER) in the index key - this makes variable length index keys and xBase can't handle that
(I even tried to do the above in Clipper code and got the same errors).
Changing it to STR(JNUMMER,10) solved the problem.
Somewhere deep in my memory I already knew this issue - it's been too long since I last used Clipper/dBase