Re: Best method for search

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Elvis,

I'm not sure that counts as "faster than 3 chains from RPG".  Given
that the use of REPLACE on the federalIDColumn would cause a full
table scan correct?  (Unless you are running v6r1 perhaps?)

Dean,

Given that you're only dealing with a max of 3 records, I doubt any
SQL method is going to be faster.

Charles

On Thu, Feb 5, 2009 at 11:55 AM, Elvis Budimlic
<ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> SELECT * FROM myFile WHERE REPLACE(FederalIdColumn,'-','') = '999999999'
>
> Hth, Elvis
>
> Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
> OS/400
> www.centerfieldtechnology.com
>
>
> -----Original Message-----
> 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.
>
>
>
> --
> 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.