The not so simple SQL Statements
Who would have thought that returning some records would become an all day ordeal. I guess if I had completely thought it through, I might have seen the roadblocks to come, but that’s not how the day went.
All I wanted to have was a drop down box that when the option was changed to a particular kind of equipment, it would bring back (through some AJAX-y goodness) the last 10 pieces of equipment in that category.
I was only working on the functionality of it at this point. The look and wording of it will change before it gets on the site.
After a few minutes I had it working. Not to difficult. Using AJAX to dynamically change the page on an ‘onChange’ event with the drop down box brought back the last 10 pieces of equipment.
It worked, but on closer examination I saw a problem. Since equipment in our database is uploaded in bulk, the last 10 machines are always from the same company, sometimes even 10 of the same model with slightly different specs. What I decided I really wanted was the last 10 machines from 10 different companies.
And THAT is harder than it sounds. For several reasons ‘Select DISTINCT’ can not be used and I hunted around the internet for a while and could find nothing that would help me they way I wanted. I was finally able to consult with our company’s sometimes hanging around .NET programmer (I hate .NET) and he recommended the ‘UNION’ function of SQL.
This sounded like a duct tape solution if I ever heard of one, but it does work.
This works by getting a list of the last 10 companies to update in the category you are working with. Then do a series of ‘Select TOP 1 *’ statements for each company that you then ‘UNION’ together to make the results look like one statement.
For example…
Select Top 1 {COL} from {TABLE} where company = {FIRST COMPANY} UNION Select Top 1 {COL} from {TABLE} where company = {SECOND COMPANY} UNION
And so on. You need to keep count of your UNIONS thought, because when you get to the 10th company, you don’t want to end your statement with ‘UNION’ because it will error out. So you pull out the good old
i = 0
i = i +1
While i < 10
We’ve all used some for of that code block no matter what language you are using to program, but it ended up not working here. It turns out that for some pieces of equipment, the items are so industry specific that there aren’t 10 different dealers. That means that the code errors out before the end of the SQL statement because there is nothing left to select.
So what needs to be done here is something along these lines….
SQL = 'Select TOP 1 {COL} from {TABLE} where company = {RECORDSET.COMPANY}'
while not RECORDSET.EOF
SQL = SQL & ' UNION '
SQL = SQL & 'Select TOP 1 {COL} from {TABLE} where company = {RECORDSET.COMPANY}'
RECORDSET.MOVENEXT
WEND
What happens here is that the SQL Statement is fired and then it checks to see if there are anymore companies. If so, it adds the UNION tag and the next SQL Select. When there are no more companies to bring equipment back from, it ends the statement and there is no error.
Since RECORDSET.COMPANY should be the last 10 companies that updated, the Select statement won’t bring back more than 10 records before it gets to the end.
Of course, that couldn’t be the final problem. Apparently, there are a lot of apostrophe’s in the company names, so it took a few minutes to figure out how to get the REPLACE function to work in this situation, but that wasn’t that big of a deal.
Here is a link to the working version of my code.
http://www.constructionequipmentguide.com/ajax.asp
When I get back to work, I will flesh this out with more accurate code descriptions
.
