Made a change to a symbolic dictionary item which has a BTREE index on it and now a SELECT with BETWEEN no longer returns the correct keys. Any ideas? I know indexes on symbolics are "iffy" but it was working fine before the change and the field is one of the most used in our system - changing all code in our system where this dict item is referenced is not a very appealing idea. Please help…

Dictionary Items Used by the offending symbolic:

BATCH

Symbolic

Single

Just - L

Len -  5

L#5

EFT_DATETIME

Single

Just - L

Len  - 15

CHEQUE.DATE

Single

Data Type - DATE

Output format - D4-

Val Pattern - (D)

Just - R

Len -  10

DATE.POSTED

Single

Data Type - DATE

Output format - D4-

Val Pattern - (D)

Just - R

Len -  10

DATE.CREATED

Single

Data Type - DATE

Output format - D4-

Val Pattern - (D)

Just - R

Len -  10

The item which when referenced no longer works properly:

TRAN.DATE

Symbolic (see code before and after below)

Single

Data Type - DATE

Output format - D4-

Val Pattern - (D)

Just - R

Len -  10

TRAN.DATE Code before change (which SELECT BETWEEN works properly):

IF {BATCH}=C" THEN

IF {DATE.POSTED} THEN

  @ANS={DATE.POSTED}

END ELSE

  @ANS='

END

END ELSE

IF {DATE.CREATED} THEN

  @ANS={DATE.CREATED}

END ELSE

  @ANS={DATE.POSTED}

END

END

TRAN.DATE Code after change (which SELECT BETWEEN does not work properly):

IF {BATCH}=C" THEN

IF {EFT_DATETIME} THEN

  IF {CHEQUE.DATE} THEN

    @ANS={CHEQUE.DATE}

  END ELSE

    @ANS='

  END

END ELSE

  IF {DATE.POSTED} THEN

    @ANS={DATE.POSTED}

  END ELSE

    @ANS='

  END

END

END ELSE

IF {DATE.CREATED} THEN

  @ANS={DATE.CREATED}

END ELSE

  @ANS={DATE.POSTED}

END

END

When issuing the command SELECT TRANSACTIONS WITH TRAN.DATE BETWEEN "01 FEB 2003" AND "28 FEB 2003" the cursor contains items which have a CHEQUE.DATE in March. I have verified that the correct date is being returned from TRAN.DATE with a LIST TRANSACTIONS TRAN.DATE. If I issue a second SELECT TRANSACTIONS WITH TRAN.DATE BETWEEN "01 FEB 2003" AND "28 FEB 2003" immediately after the first selection, the offending keys are removed from the cursor (as it appears the second select is not using the index). I have also confirmed that the problem lies with the index by issuing the command SELECT TRANSACTIONS WITH TRAN.DATE.UNINDEXED BETWEEN "01 FEB 2003" AND "28 FEB 2003" which returns the correct keys. (the dict item TRAN.DATE.UNINDEXED is exactly that - @ANS={TRAN.DATE} but the field is not indexed. I have forced an update by INDEX TRANSACTIONS TRAN.DATE BTREE with no effect on the returned cursor. I have not yet tried rebuilding the TRAN.DATE BTREE index but will do so tonight. Anyone have any ideas what could be going wrong?

Rebuilding the index will probably solve your problem.

Batch is a symbolic. Is this referencing another file?

As mentioned elsewhere, your problem is resolved by rebuilding the index.

The issue is that the symbolic doesnt 'know' that a dependant data field has been changed, so to can't by itself get a recalculation on the paticular data –] new index value. The gross workround is to REBUILD the index.

However you could establish a dependency which the mfs will use - as if it where a real field. Have a look for the proceedure at Sprezzatura's web site - look in their Revmedia - its documented there. Involves just setting one or two values in the symbolic dict. Problem solved.

Thank-you both for your suggestions. Rebuilding the index did indeed solve the problem.

Note that you will have to do a rebuild every time the calculated value of the symbolic changes. This may not be an issue. However if this is a frequent occurence, un-predictable, then follow up the workround which establishes a dependency trigger as suggested in the Revmedia article. The index doesn't rebuild in this senario, only the changed value.

Richard Bright

BrightIdeas New Zealand

As Warren … if {BATCH} is calculated only from values in the @record … it will be OK, but if it reffers to another

record(s)/table(s), the BTREE will not work properly … no transaction will be generated when BATCH changes from "C" to something else or vice versa … and therefore INDEX cannot be properly updated.

View this thread on the forum...