Re: Best method for search

Dean.Eshleman wrote:

I'm trying to determine the best way to find data in a field. Either
RPG or SQL. First some background. We have a field called Federal
ID number that contains multiple types of numbers. There is an
associated field called Federal ID type which identifies what kind of
ID number it is. The possible values are Canadian SIN (999-999-999),
Employer ID (99-9999999), SSN (999-99-9999) and Medicare # which has
the same format as SSN. The Federal ID number field is alphanumeric
and it contains the dashes. We have a logical file defined with
Federal ID number as the key.

Now my question. Given a 9 digit number what is the best/fastest way
to find the record that matches? One option is to use RPG and format
the number 3 different ways and chain to the file 3 times. Would SQL
provide a better solution performance wise? I guess another option
would be to store the Federal ID number without the dashes in a
separate field. I'm trying to avoid that if possible. TIA

The SQL can be done the /same/ as the proposed RPG, by generating the same three literals to match against. However, by any chance does the searcher know in advance, which ID type is being searched? If the type of ID number to be searched is known, then in both cases [CHAIN or SQL], the Federal ID could be formatted for the search directly. It actually seems unlikely that the type of ID being searched would be unknown, since receiving multiple rows for the inquiry would presumably lead to confusion?

create table FIDtable
( FIDnbr char (11) /* left justified ID nbr w/ dashes*/
, FIDtyp char (01) /* C, E, S, M */
, FIDstuff varchar(77) /* something to select by ID */
, constraint FID_PK primary key (FIDnbr /* , FIDtyp */)
, constraint FID_typ check (FIDtyp IN ('C','E','S','M'))
/* CANada SID=C, EMPloyer=E, SSN='S', MEDicare='M' */
)

-- Generate the three literals to search on, for 123456789:
select FIDstuff from FIDtable
where FIDnbr IN ('12-3456789'
,'123-456-789'
,'123-45-6789')

-- Known as a search for SSN, format 132456789 as SSN:
select FIDstuff from FIDtable
where FIDnbr = '123-45-6789'
and FIDtyp = 'S'

A better search than just the REPLACE suggested by Elvis, is to improve the selectivity of the data where the function would be used; that is, to take advantage of the partial key, to some level of consistency across the varying formats:

-- Somewhat selective to encourage index; EVI or standard:
select FIDstuff from FIDtable
where FIDnbr LIKE '12%'
and REPLACE(FIDnbr,'-','')='123456789'

-- Very selective, to six bytes of eleven bytes; any more
-- selective means three exact literals, like first example:
select FIDstuff from FIDtable
where ( FIDnbr LIKE '12-345%'
or FIDnbr LIKE '123-45%' )
and REPLACE(FIDnbr,'-','')='123456789'

-- Intuitively from above, first format is unique of four;
-- next two formats restricted to first six bytes as above:
select FIDstuff from FIDtable
( FIDnbr LIKE '12-3456789 '/* =|LIKE equivalent here */
or FIDnbr LIKE '123-45%') /* do not use '123-45__789'*/
and REPLACE(FIDnbr,'-','')='123456789'

Regards, Chuck