Re: Best Practices Question

You could store both the 11 and 12 digit account numbers on the nt server. An insert trigger could fill the 12 digit account number for new records. It still requires a change to sql statements but it may be more palatable than the function approach you mentioned.

Gene Hubert
Durham, NC

"Patrick Meyer" <buckeye234_at_excite.com> wrote in message news:<8549701170293b6ee7e355bb7d982310.61632_at_mygate.mailgate.org>...
> We have a large database for Oracle's OFSA software. It's on a UNIX box
> and uses Oracle 8.1.6 for the database. We also have a smaller
> reporting database, also 8.1.6, on an NT server for the end users.
> There
> is a large table in the UNIX database that has an account number column
> that is 12 characters long. On one of the NT tables, there is an
> account number column that is 11 characters long. Upon investigation, I
> have found that the first 11 characters of the account numbers are
> identical and that the 12th character on the UNIX database account
> number column is some sort of "check" digit and is generated on the
> mainframe. The mainframe is the source of information for both
> databases, but the data goes directly from the mainframe to the UNIX
> database and is filtered through multiple systems, which strip off the
> check digit, to the NT database.
>
> A user wants to run a query on the NT database that joins the NT table
> with the UNIX table, on the account number column. Currently part of
> the where clause looks similar to nt_table.account =
> substr(unix_table.account,1,11); This of course causes a full table
> scan on the unix table. My question, (Finally!!) is what is the best
> approach to solve this? Neither database owner wants to change the
> format of their table to accomadate the other. We could create a
> function based index on the UNIX table to have an index that corresponds
> tp the where clause, substr(unix_table.account,1,11), but special
> handling will be required to minimize impact on an already slow monthly
> download to the UNIX database.
> Another option is to create a function in the NT database that
> calculates the check digit and use this in the where clause, so that it
> looks like:
> Calc_Digit(NT_Table.account) = UNIX_Table.account, but the users don't
> like this either. So, again, what is the Best Practice for handling
> this request specifically, and other requests like this in general.
>
> I appreciate any input form all sources. Thanks in advance.
>
> Later ......
> Patrick
Received on Wed Feb 13 2002 - 14:19:32 CST