One Case – The Daily WTF

I feel like we’ve gotten a few SQL case statement abuses recently, but a properly bad one continues to tickle me. Ken C sends us one that, well:

SELECT CASE h.DOCUMENTTYPE
        WHEN 2 THEN 3 WHEN 3 THEN 4 WHEN 4 THEN 5
        WHEN 5 THEN 6 WHEN 6 THEN 7 WHEN 7 THEN 8
        ELSE h.DOCUMENTTYPE
    END AS DocumentType,
    h.DOCNMBR AS DocNmbr,
    h.FULLPOLICY AS FullPolicy,
    h.BATCHID AS BatchId,
    h.OrigBatchId,
    h.UPDATEDDATE AS UpdatedDate,
    h.CUSTOMERNO AS CustomerNo,
    h.PROJECTID AS ProjectID,
    h.AMOUNT AS Amount

On one hand, I can’t say “just add one”, because clearly sometimes they don’t want to add one. On the other hand, there’s an element of looking at this and knowing: well, something absolutely stupid has happened here. Maybe it was two disjoint databases getting merged. Maybe it was just once upon a time, when this database was a spreadsheet, the user responsible did a weird thing. Maybe some directive changed the document type numbering. Hell, maybe that ELSE clause never gets triggered, and we actually could just do arithmetic.