In the first article of this topic, I explained the difficulties I ran into trying to assign customer aliases while importing Customer Project Info records into PA00501. In this article I'll present one possible solution for generating customer aliases that does not require any looping in my .NET code, automatically fills in gaps in the numbering sequence, and only requires one call to the SQL database.
I only recently discovered this approach (which appears to be a standard approach to the problem that many before me have already conquered), so I haven't yet thought of possible drawbacks or complications, but so far it seems to work in concept, and even better, it is a general solution that can be applied to many different situations where you need a robust, flexible, and fast method for getting a next number.
So I was already pretty convinced that looping through an arbitrary list of customer alias options was not practical, and that I was going to have to come up with a more comprehensive solution. What is the alternative to taking a subset of choices and checking each one of them against the list of existing customer aliases?
How about if we reverse the situation. Instead of asking "Is this single value already used?", what if we ask "What are ALL of the values that are already in use?". For that new question to make sense, we have to provide some context, so let's try "Out of all of the possible options of our entire value set, what are all of the values that are already in use?" Huh?
Let's consider that our entire set of possible customer aliases is 00000-ZZZZZ. So that's a "base 36" numbering scheme, which gives us 60,466,176 possible permutations. More than enough for any real GP customer. Hmmmmm. So instead of generating a single value, and then checking it against my alias list, what if I had a list of every single possible alias value at my disposal? Well, first of all, it means that I don't have to write a routine to generate a base 36 number, which is a pain, and I could therefore also probably eliminate any looping code. But how in the world could I possibly deal with a list of 60 million values?
If I think "procedurally", I know that I can't possibly loop through that many values. So loops and cursors are out of the question. But if I think in terms of "set based" operations, where SQL Server will magically sift through my request in a split second, the number of records effectively becomes irrelevant. (There are practical limitations that I'll consider later, but bear with me for now.) Okay...so...if I have a list of every possible customer alias, and I also have a list of every customer alias that has already been assigned in GP, what does that mean? It means that I can use a set based operation to compare the two lists, aka a SQL JOIN! And, if I'm able to compare the two lists using a JOIN, that also means that I can compare them in two different ways: An inner join, and an outer join. This was the "Eureka!" moment at which I knew there was hope.
If I can do an outer join between the two lists, that means that SQL Server will instantly tell me…