Re: DB Schema suitable for searching

This means every open invoice appears in this table 4 times, once for

each SENTTP.

The app uses SQL to search for possible matches.

Charles,

I'll also suggest an old technique used since the days of the System
/38.  For the alpha fields, you can re-map them so each alpha field in a
record orients to the search field.  I have used this technique for Unit
of Measure Lookups, (LB->OZ and OZ->LB) and a Warehouse search, where
the users could key in the name of the City or the name of the Warehouse
Company, and the record was brought in both ways.

This is a multi-format Logical over the same Physical File.  With
logicals like this, you do need to check the format name of the record,
but it works great because the index is a permanent index in a logical
file.  Here's some sample LF DDS that I have handy, where we can lookup
in Personnel using the SSN of a Retiree or SSN of the Spouse of the
Retiree
 R CURNTMPM3E                PFILE(CUPNTMPM)
  PMSSN
  PMPNAME
  PMCNM
  PMSSSN

 K PMSSN
 K PMPNAME

 R CURNTMPM3S                PFILE(CUPNTMPM)
  PMSSN                     RENAME(PMSSSN)
  PMPNAME                   RENAME(PMSNAME)
  PMCNM
  PMSSSN                    RENAME(PMSSN)

 K PMSSN
 K PMPNAME

I know it may not be a solution you are looking for, and will probably
require an app rewrite to do it with logicals, but performance-wise, it
cannot be beat.  There are similar ways to do this in SQL with permanent
indices built, but I just don't have a solution of that handy today.
And SQL has better Numeric-to-Alpha conversions than DDS.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.