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.



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



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

    or 

    set @foo = @amount?

     



  • 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();




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

     

    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


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.