RE: Best method for search

Dean,

Looking at your other option regarding storing the federal ID number without
dashes, you could take Elvis' idea using the replace function and do just
that by creating a SQL view.

CREATE VIEW library/idxref AS
SELECT REPLACE(federalid,'-','') AS idnodash,
federalid
FROM library/myfile

You could then take your 9 digit number (no dashes) and access that view and
retrieve the corresponding federal ID number (with dashes).

There are probably still some performance issues to evaluate.

Also, could there be a case of duplicate records? For example SSN
123-45-6789 and Employer ID 12-3456789 would be a unique key in your file.
However, if I want to look up the record for number 123456789, which record
should I get!?

Have fun!
Richard

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Dean.Eshleman@xxxxxxxxxxxxxx
Sent: Thursday, February 05, 2009 11:28 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Best method for search

Hi,

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 seperate field. I'm
trying to avoid that if possible. TIA

Dean Eshleman,
MMA, Inc.