RE: SQL Query Scalar Function Versus Second Table

[qsrvbas@xxxxxxxxxxxx (Tom Liotta)]

midrange-l-request@xxxxxxxxxxxx wrote:

>   9. Re: SQL Query Scalar Function Versus Second Table
>      (Narayanan R Pillai)
>
>Thanks for that warning. Luckily there are no partNumbers which are 
>identical except for dashes.
>
>On further conversations with the users, it turns out that the problem 
>seems to be when some user's are entering partNumber's like A-123300 for 
>a partNumber A-123-300. Or even more frequently,  A123300  ( like it is 
>said )  instead of  A-123-300.
>
>I would be inclined to go with the replace( partNumber, '-', '' ) 
>solution, if it were not for the fact that it might impact retrieval 
>speeds, and I am leery of building an index on replace( partNumber, 
>'-','' ) because of the performance implications.

Pillai:

Are the dashes always in the same place? Is data entry on a green-screen? If 
both are 'yes', then consider simply masking data entry of part number so the 
user never enters a dash -- that is, use the EDTMSH() keyword.

If either is 'no', then get the dashes out of the key to begin with. Don't 
store the dashes in the key field; create a new column if really necessary to 
hold dashes in the file, but use the non-dash field as the primary index. The 
second field could be a secondary key that could be used for convenience I 
suppose.

You could create a related table that has the two columns if you can't change 
the existing one.

Tom Liotta