Can't print 100

djork

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.

The_Vicar

@djork said:

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...

zip

set @foo = select count(Qty) From [quantity] where Qty = @amount

or

set @foo = @amount?

lpope187

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,

While (reader.Read())

{

... snip ...

Credential.Print();

}

RayS

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!

bifel

i'd say, they wanted to achieve something like:

insert into sometable (...) select ... from ..., quantity where quantity.qty=<quantity here> and ...

Markp

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.

So like

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

or

SELECT TOP 100 rowtoprint FROM table