btree search on a calculated column? (OpenInsight Specific)
At 01 MAY 1998 06:36:27AM J Ignatius wrote:
Is it possible to perform a btree search on a calculated column in a table?
At 01 MAY 1998 10:11AM Aaron Kaplan wrote:
Yes, but the results might not be what you want, depending on how the column is calculated. XREF indexes are Btree's on a calcuated column, as an example.
apk@sprezzatura.com
At 01 MAY 1998 11:02AM J Ignatius wrote:
When I defined a btree index on a calculated column it didn't return the values I was expecting. I tried defining a cross reference index and then doing a btree search but I still don't get the values I'm looking for.
Any suggestions?
At 01 MAY 1998 04:20PM Cameron Revelation wrote:
J Ignatius,
A BTREE index stores the values of the indexed columns as they were when the record was saved. If your calculated column pulls data from elsewhere or does other calculations that are not based simply on @id/@record, then don't BTREE the calculated column.
Cameron Purdy
Revelation Software
At 02 MAY 1998 09:32AM Aaron Kaplan wrote:
It would help if you explain what you are doing.
apk@sprezzatura.com
At 02 MAY 1998 09:42AM Richard A. Bordow wrote:
One should be very careful about BTREEs on XLATE fields.
This has been a very long term problem going back to arev 1.16.
For example:
A. I have two files Patient and Office_Visits.
B. The Key to Office_Visits is Visit_No.
C. The Key to Patient is Patient_No.
D. The Field Lastname in the Office_Visits file is (i) an XLATE to the Lastname field in the Patient File and (ii) is "BTREED" in the Office File.
E. A lookup done in the Office_Visit file against a "changed" name in the Patient file will not find any Office_Visit records.
F. The only solution in AREV and OI 2.5 to current version is to re-read the Office_Visit records, delete them or delete the lastname field, and rewrite them.
This is a pain, of course, but it works.
It would be great if a more elegant solution could be provided!
At 02 MAY 1998 03:09PM Andrew P McAuley wrote:
We used to rely on the "dependant index" logic to do this automatically (see RevMedia passim) but it seemed to stop working in one release. So we added our own MFS to automatically add the index transaction rows when things were changed. Not a huge deal of work.
amcauley@sprezzatura.com
World Leaders in all things RevSoft
PS How's it going?!