View Full Version : TIP: Conditional Indexes


Melvin Davidson
11-23-2000, 07:53 PM
Although not apparent, you can use a conditional expression
to create an Index for a database and/or set.

I have an audio database structured as follows.

ID Character 5 0
TYPE Character 1 0
TAPE_NO Numeric 3 0
TAPE_SEQ Numeric 2 0
ARTIST Character 50 0
ALBUM_NAME Character 50 0
COMMENTS Memo 10 0

The ID is constructed as follows.
1st character is R for Record or T for Tape
2nd character is the 1st letter of the Artist
3rd - 5th is a sequence # to make the id unique.

The TAPE_NO & TAPE_SEQ will only have information if it is a Tape.

I wanted to make an index that would put the tapes in sequence by TAPE_NO & TAPE_SEQ.

Using STR(TAPE_NO, 3) + STR(TAPE_SEQ,2) will not work because all the Records will be out of sequence.

The solution is a conditional index.

IIF(LEFT( ID, 1)="T",
LEFT( ID, 1) + STR(TAPE_NO, 3, 0)+STR(TAPE_SEQ, 2, 0),

That orders the tapes correctly and keeps the records in line also.

Hopefully this information will be helpful to you if you have a similar