The LOGIC Behind Mad MAX()
This article concentrates on assigning the next sequential customer number in a mixed use CHAR column. The F1:'Books Online' explanation is worthless & I couldn't find anything in MSDN or on the web about this.
If any of you have tried sorting numbers in a text coulmn with TSQL MAX() function, You’ll know this. But I lost almost a week on a project because this particular flavor of MAX works a little different than MAX() in other programming languages. Not just the syntax - under the hood they're completely different. I ended up eating almost 30 billable hours, in total. A good two solid days was spent intimately learning the nuances of this little bugger.
Recently we were commissioned by a customer to integrate his web shopping cart and accounting system. Maybe “Integrate” is not a good word because they wanted to keep costs down; they didn’t care if it was real-time; but, it needed to be idiot-proof. In other words: when we said "There are 3 ways to do it. Cheap, Fast, or Good: Pick any 2 out of 3", They said they they wanted "ALL 3". I hate when that happens...
In order to keep things fairly simple (FAST), we decided to have the web guys create a CSV file that we can import periodically. We didn’t want to reinvent the wheel (CHEAP) so we’d taken the built-in AccountMate Import Sales Order Form and modified it for their needs. Basically we simplified the user interface that was designed to be able to import many formats, and created a new one that a caveman could use (GOOD). We called it the Web Import Wizard. The specifications are detailed below.
Esssentially, we were building an applet that would import data from the web shopping cart so that the user can ship a Sales Order (SO) to create a Receivable (AR) Invoice rather than manually entering them. This would also create Pick Lists, Packing Lists and simplify Inventory Control (IC) functions. The accounting system framework is the latest version of AccountMate for MS-SQL so the front-end was written in Visual FoxPro v9 (VFP) AND luckily, the customer also had the option and the foresight of licensing the SO, AR & IC source code. The back end of the framework is MS-SQL2005. All of the reports mentioned earlier were done in Crystal XI.
First off we needed to select the import file & parse it. We looked to see if the end user’s eMail Address was on file. If it was, we’d use the existing customer number, otherwise we’d create one, populate or update the customer record (BillTo, ShipTo, Etc.)accordingly, and then process the line items before moving on to the next record. When we had to create a NEW customer record – things got a bit trickier.
When the FoxPro guys modified the screen-form they created a function in FoxPro, GetNewCustNo(), to get the next sequential customer number. What this function needed to do was fairly simple:
- Get the largest numeric customer id (cCustNo) from a table called arcust
- If it’s empty default to ‘100000’
- Convert that CHAR data to a Numeric datatype so we can perform math
- Increment it by 1
- Convert it back to CHAR
Like many other modern languages VFP has a SQL Pass-Through function so that we can execute TSQL inside of our shell. Here’s the VFP code:
local lcCustNo
if GetSqlData("SELECT isnull(Max(cCustNo)),'') as cCustNo " + ;
"FROM arcust ", "CurArcust") &&, lnHandle)
lcCustNo = iif(CurArcust.cCustNo = ' ','100000',alltrim(str(val(CurArcust.cCustNo) + 1)))
else
lcCustNo = '100000'
endif
return lcCustNo
This worked awesomely, but only if we started with an empty Customer table – and it bombed when we put it into production: Primary Key Violation! Apparently there is an issue with the existing customer's data but there is also something strange going on with the tsql MAX() function - after several hours of troubleshooting I noticed that some of the custNo's were not numeric – Well, there’s the pKey Violation. I plugged the pass-through query into Query Analyzer & found that it was ALWAYS coming back as '9999' which was a test company the client was using. What confused the crap out of me was that there was a customer that was numbered ‘63492’. Why wasn’t it finding it?
Long story short: I tweaked the VFP GetNewCustNo() method & I converted ccustno to an integer (actually CAST ccustno AS INT) & then back to CHAR on the back end - via the SQL passthru in VFP.
Basically I just changed the first line to this:
if GetSqlData("SELECT CAST(isnull(Max(CAST(cCustNo as INT)),'') as char(10)) as cCustNo
FROM arcust WHERE isnumeric(cCustNo) = 1", "CurArcust") , lnHandle)
THIS WORKS. But now, I’m truly baffled. I'm not sure why it works b/c I don’t understand why it broke in the first place. So, I test it on production data anyway. Alright - everything works OK in two of the sample companies... but, not for production. I feel like Charlie Brown when Lucy is holding the football. AAAAAAAARGH!
Again, Primary Key Violation – I’m thinking ‘customer numbers are not sequential’ and/or ‘they weren’t all numeric’ (but I killed the alphas with isNumeric=1, didn't I?). The only thing left is that the client side code we were using to get the str(val(MAX(arcust.ccustno)))+1 was bombing with a company that was called 'TEST' but that makes no sense b/c SAMPLE company is using ALPHA in ccustno, too. Hmmm...
There’s no doubt there is an issue with the existing customer's data but there is also something really strange going on with the tsql MAX() function - after more troubleshooting I noticed that all of the custNo's were going in as '100001' – Well, there’s the pKey Violation. I plugged the pass-through query into Query Analyzer & found that it was ALWAYS coming back as ' ' which made no sense. Why the heck was it bombing?
As much as I'd love to blame the customer or the data, I cannot. It's not even a Microsoft bug! It is the same in sql2005 AND it seems to be by design. Here's what's going on... when SQL compares the value of text strings of varying lengths it only looks at the first n characters (where n is the smallest LENgth of the series) AFTER trimming the spaces from both left & right.
To prove this, try running the following tsql:
IF '9999' > '63492' PRINT 'THIS APPEARS TO DEFY LOGIC!'
There it was! I had the epiphany: <city w:st="on"><place w:st="on">EUREKA! -- That's the issue...
It's not comparing numbers - it's sorting text!
It makes perfect sense if you were alphabetizing a list; 'zylch' is greater than 'zoo' but both are greater than 'abracadabra' - even though the lengths are all different. It's a standard BubbleSort - so, it's really only comparing 2 at a time.
Now, this just plain sucks when we're really trying to compare (the numeric equivalents of converted) character values (that are stored in a mixed use column) unless you: throw tons of code at it (i.e. pad the left with zero's; CAST or CONVERT to some numeric datatype then compare and then back to a character datatype - which is what I did earlier). But, that can cause all kinds of other crap to blow-up down the road, like: what if it won't convert b/c of illegal characters? Figuring: SQL will either try to do the math first then convert the result to char or bomb completely depending on the string. Again... too much freakin' code! I'd much rather waste my time w/long-winded explanations... (like this one!)
Here's what I came up with:
SELECT STR(isNull(Max(CAST(cCustNo AS Int)+1),'100001')) as cCustNo
FROM arcust WHERE isNumeric(cCustNo)=1
Brilliant! I’ve even eliminated the conditional IF and IIF statements. This works, but it bombs if someone throws a period into the character string (Can’t Convert Float to Integer). So... I modified it to the following:
SELECT STR(isNull(Max(CAST(cCustNo AS Decimal(10,0))+1),'100001'))
AS cCustNo FROM arcust WHERE isNumeric(cCustNo)=1
I believe this is a grand-slam b/c it's pure tsql & always returns the biggest integer value incremented by 1 - even if someone creates a customer # with a period in it or any other characters. As a bonus, It can allow us to replace the whole GetNewCustNo() function with a single line of code. It can even be used as part of the INSERT INTO so that you eliminate the possibility of a CustNo collision b/c two users have run the code at almost the same time. It can all happen on the server-side - where it should. - whether I'm writing in any of the flavors of VFP, VB, C, or Java.
I’ve got a funny feeling there’s some other type of invalid character data that can creep in there & cause it to fail. But that’ll be another article…
Of course, the client wouldn't pay for the billable hours spent retrofitting it into his existing data. So, you guys get this lesson for free. If it saves one person an hour, I'll be paid back in kharma. The moral of the story: Don't do what I did!
~wiz
