I've a JOBS table and INVOICES table. Both keys JOB_NO and INVOICE_NO default to using a SEQKEY, but the user may enter alpha characters (rarely done) if desired and hence are both VARCHAR and Right Justified within Table definition. The JOB_NO field has a Btree index on it, INVOICE_NO does not.

Example 1:

If I do a REDUCE for JOBS table with criteria: WITH JOB_NO BETWEEN 1 AND 10 it returns "1" and "10" ie. comparing from the left.

Example 2:

If I do a REDUCE for INVOICES table with criteria: WITH INVOICE_NO BETWEEN 1 AND 10 it returns "1","2","3"…"10" ie. comparing from the right (which is what I'm after.)

What I'm after is a way to do the comparison on JOB_NO as per INVOICE_NO (ie. treat it numerically) without removing the BTREE index from JOB_NO.

NB. Using greater/less than instead of BETWEEN yields the same results.

Thanks in Advance,

James.

James,

What about doing it via a symbolic?

Alternative 1: Since Btree-indexed INTEGER fields seem to behave better than Btree-indexed VARCHARs, use a Btree-indexed symbolic INTEGER field BETTER_NO that contains only the number values from JOB_NO. All other values must be "" or 0. With this alternative you can only search for number ranges, of course.

Alternative 2: Use a Btree-indexed symbolic VARCHAR field BETTER_NO that contains a fixed length, zero-filled, right-justified version of the JOB_NO numbers (e.g "00001", "00123", "04711", …). Let other values be as they are. Of course with this alternative you also have to convert the search values you use in your Reduce statements.

- Oystein -

Alternative #3:

Me thinks your scenario should work, especially if the INVOICES key works correctly. My suggestion is to rebuild the index from scratch and make sure the master dictionary for the key is set to 5+ characters longer than the longest key. I'm wondering if once upon a time this key was left justified but was changed over without the indexes being rebuilt.

dbakke@srpcs.com

SRP Computer Solutions

Thanks Don,

I suspect you may be physic… I suspect the field in question WAS left justified at some stage. Rebuilding the index did not work, but removing it, then readding it did.

Do you have any suggestions as to how I should update the indexes for existing clients?

Thanks again,

James.

Thanks for your ideas Oystein, but I didn't follow the problem though enough…

James,

What is your normal method of deployment and distribution (i.e. RDK, Check-Out, etc.)?

dbakke@srpcs.com

SRP Computer Solutions

View this thread on the forum...