Can't print 100
So, someone stumbled upon an interesting problem here at our organization: they couldn't print 100 of a particular type of credential we produce. They could print one, two, even 99 records, but as soon as it went above 99 it simply failed. Why?
Further inspection of the database yielded a table buried deep in the schema. This table is called "quantity." Buckle up now.
SELECT * FROM [quantity] (4950 row(s) affected) Qty 1 2 2 3 3 3 4 4 4 4 5 5 5 5 5 6 6 6 6 6 6 7 7 7 7 7 7 7 8 8 8 8 8 8 8 8 ...
Yes, the single column in the table (Qty) held a number. There are exactly that number of rows for that quantity. There are 99 99's, no more, no less. Apparently, 99 was chosen as the limit for "performance reasons." God only knows how this table is used or why. Myself, I'll just try not to exceed 99.
Yes, the single column in the table (Qty) held a number. There are exactly that number of rows for that quantity. There are 99 99's, no more, no less. Apparently, 99 was chosen as the limit for "performance reasons." God only knows how this table is used or why. Myself, I'll just try not to exceed 99.Betcha anything you like that this table is being used to convert strings to integers -- the user makes a choice, and instead of converting the choice to a number directly, the program is making a query "SELECT * WHERE Qty = user_selection" and then counting the number of results...
Another possibility is that it is used to build a "print queue" recordset. For example,
SELECT Credentials.* FROM Credentials INNER JOIN Quantity on Credentials.quantity = Quantity.quantity
Then in the application they do,
... snip ...
SELECT *, 'Repeat this string X number of times' as Header FROM Quantity WHERE Qty=X
Well that's one way to duplicate a string X number of times. It's a pretty poopy-headed way, but a way it certainly is.
Thankfully SQL will scale up nicely to 999 and beyond if you need it to! All hail SQL abuse!
i'd say, they wanted to achieve something like:
insert into sometable (...) select ... from ..., quantity where quantity.qty=<quantity here> and ...
My guess is that the person designing the queries couldn't figure out how to limit the number of records returned for whatever vendor he was using and so found some obscure JOIN that would do it for him, in conjunction with that table.
WITH cte AS (SELECT 99 as qty, table.rowtoprint FROM table ORDER BY rowtoprint ASC) SELECT * FROM quantity LEFT JOIN cte ON quantity.qty=cte.qty WHERE quantity.qty=99
Or something along those lines, instead of say
SELECT * FROM table WHERE rownum < 100 for Oracle
SELECT TOP 100 rowtoprint FROM table