Sql query wtf



  •  

    I found this while fixing a stored procedure.  The query is used to get the distinct values of a specific column, but only if its one of the hard coded values.

     

    SELECT Distinct Location AS [Value] FROM ClientTable WHERE ID = 999
    AND Location in ('CE','East','MA','NW','NE','SE','Southeast','WE','West')
    ORDER BY Location
    

     

     Basically it will always only return the hard coded values, but still preforms a query to pull just those values.

    wtf?

     



  • But it'll only retreive them if they are actually in the table, maybe they want a list of which locations have been used (eg, where items have been shipped to). As for restricting the options, possibly there's a lot more options such as 'Overseas' or whatnot that they don't want included.



  • Non-WTF.

    Consider if the entire contents of ClientTable look like this:

    ID Location
    999 CE
    999 East
    999 West

    In this scenario the given query will not return the entire list of hardcoded values, but instead only "CE", "East", and "West".



  • I guess i forgot to mention that it is used to fill a dropdown box with all the possible locations, which is only the ones in the parenthesis.

    So, really if a value was missing, we would have a problem with missing locations.
     



  • Design by accretion.

    "Users are entering random locations into the field in our database. Make it a select box." Hence the "SELECT DISTINCT" query.

    "The select box includes a lot of bad locations. Make sure it only contains one of these valid locations." Hence the list.

    While there's certainly a better solution to the problem once you know the whole thing, each individual piece of the problem has been solved quite efficiently along the way.

    It's still a WTF, but we all end up having to do this at some point. You get that promise every couple weeks that you can go back and redesign Real Soon Now, as soon as we finish tweaking this one last feature.

     


Log in to reply