Re: SQL Query Scalar Function Versus Second Table

["Carel Teijgeler" <coteijgeler@xxxxxxxxx>]

Pillai,

So, part number 123-3321-000 will become 1233321000. Part number 1233-3210-00 
will become 1233321000. Both may be correct part numbers. Looking it up in this 
special file with the value 1233321000 will retrieve the correct part number. 
Is this the idea behind this exercise?

If my understanding of the problem is correct, then:
Is it not more sensible to store the stripped value in the main file, the part 
number file, in stead of a look-up file to get the real part number value? 
Input by users should be verified with the main file, not by a "translation 
file". If part numbers do not have a regular format, so be it, but do not yield 
to the stupidiness of users, who do not create their own convention in naming 
part numbers.

Indeed, lots of work for something completely stupid; just a waste of time, as 
you will never get it completely covered. Now there are dashes, next 
underscores, you name it.

And the trigger is fired at an update of the file. What will you then, correct 
the input? And will that correction supply the required part number? Perhaps 
they fill in a random part number for deletion, the trigger selects the wrong 
one and who will be blamed? You.

The "translation file" will be contain contain a multiple of records to the 
part number file. For what? For you not allowed to verify input?

This is kind of GIGO, IMHO.

Carel Teijgeler

*********** REPLY SEPARATOR  ***********

On 22-8-05 at 10:20 Narayanan R Pillai wrote:

>This is correct, the users cannot guarantee that there will even be 
>dashes. At this point in time, the solution that seems to be acceptable 
>to all is a trigger that will remove all dashes and store that in a 
>different file, and all programs that need to validate will validate 
>against that. Unfortunately, we will have to take care of updates, inserts and 
>deletes.
>
>Lots of work for something that is not that often used :(