Nested Case Statements



  •  Had to share this snippet of SQL eyesore from our "Senior Reporting Analyst"...

     

     <!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:DontVertAlignCellWithSp/> <w:DontBreakConstrainedForcedTables/> <w:DontVertAlignInTxbx/> <w:Word11KerningPairs/> <w:CachedColBalance/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="&#45;-"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true" DefSemiHidden="true" DefQFormat="false" DefPriority="99" LatentStyleCount="267"> <w:LsdException Locked="false" Priority="0" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Normal"/> <w:LsdException Locked="false" Priority="9" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="heading 1"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/> <w:LsdException Locked="false" Priority="39" Name="toc 1"/> <w:LsdException Locked="false" Priority="39" Name="toc 2"/> <w:LsdException Locked="false" Priority="39" Name="toc 3"/> <w:LsdException Locked="false" Priority="39" Name="toc 4"/> <w:LsdException Locked="false" Priority="39" Name="toc 5"/> <w:LsdException Locked="false" Priority="39" Name="toc 6"/> <w:LsdException Locked="false" Priority="39" Name="toc 7"/> <w:LsdException Locked="false" Priority="39" Name="toc 8"/> <w:LsdException Locked="false" Priority="39" Name="toc 9"/> <w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/> <w:LsdException Locked="false" Priority="10" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Title"/> <w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/> <w:LsdException Locked="false" Priority="11" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/> <w:LsdException Locked="false" Priority="22" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Strong"/> <w:LsdException Locked="false" Priority="20" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/> <w:LsdException Locked="false" Priority="59" SemiHidden="false" UnhideWhenUsed="false" Name="Table Grid"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/> <w:LsdException Locked="false" Priority="1" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 1"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 1"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 1"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/> <w:LsdException Locked="false" Priority="34" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/> <w:LsdException Locked="false" Priority="29" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Quote"/> <w:LsdException Locked="false" Priority="30" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 1"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 1"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 2"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 2"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 2"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 2"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 2"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 3"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 3"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 3"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 3"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 3"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 4"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 4"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 4"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 4"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 4"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 5"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 5"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 5"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 5"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 5"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 6"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 6"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 6"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 6"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 6"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/> <w:LsdException Locked="false" Priority="19" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/> <w:LsdException Locked="false" Priority="21" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/> <w:LsdException Locked="false" Priority="31" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/> <w:LsdException Locked="false" Priority="32" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/> <w:LsdException Locked="false" Priority="33" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Book Title"/> <w:LsdException Locked="false" Priority="37" Name="Bibliography"/> <w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]>

    <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} </style>

    <![endif]-->

    CASE WHEN RTRIM(inv_unit_dtl_tbl.Description_vc) = 'Energy-Fixed Price Fixed Volume Off Peak' THEN 'Off Peak Contract Quantity'

          ELSE CASE WHEN RTRIM(inv_unit_dtl_tbl.Description_vc) = 'Energy-Fixed Price Fixed Volume On Peak' THEN 'On Peak Contract Quantity'

          ELSE CASE WHEN RTRIM(inv_unit_dtl_tbl.Description_vc) = 'Energy-Day Ahead Price On Peak' THEN

                CASE WHEN SUM(inv_unit_dtl_tbl.Volume_n) >= 0 THEN 'On Peak Over Usage'

                ELSE 'On Peak Under Usage' END

          ELSE CASE WHEN RTRIM(inv_unit_dtl_tbl.Description_vc) = 'Energy-Day Ahead Price Off Peak' THEN

                CASE WHEN SUM(inv_unit_dtl_tbl.Volume_n) >= 0 THEN 'Off Peak Over Usage'

                ELSE 'Off Peak Under Usage' END

          ELSE RTRIM(inv_unit_dtl_tbl.Description_vc) END END END END) AS Description_vc



  • OK, yeah, that's ugly, but SQL isn't a general-purpose programming language.  Is there a better way to accomplish what he's trying to accomplish here?

    Personally, I'd most likely just do this part of it on the client side, but if you have to do it in SQL, is there any better way?



  • That's some of the worst Hungarian naming I've ever seen, and bad indenting as well. The whole concept of using a case based on a description field to show a prettier description sounds like a really bad idea. Also, the nested cases seem to be unnecessary.



  • @matthewr81 said:

    Had to share this snippet of SQL eyesore from our "Senior Reporting Analyst"...

    What's he reporting to? The insane asylum?

    (Note to pedants: yes I know what "Senior Reporting Analyst" means. The above is a joke. You're supposed to laugh when you see jokes, and not "correct" them.)



  • @blakeyrat said:

    What's he reporting to? The insane asylum?
     

    Not all of us report and I take offense to the assumption that we have to.

    (pedantry)



  • @Jaime said:

    the nested cases seem to be unnecessary.
     

    Yeah, I would have done it like this; easier to read.

    <style type="text/css"> span { font-family: 'Courier New'; font-size: 10pt; color: #000000; } .sc0 { } .sc4 { color: #FF8000; } .sc5 { font-weight: bold; color: #0000FF; } .sc7 { color: #808080; } .sc10 { font-weight: bold; color: #000080; } .sc11 { } </style>

    CASE
        WHEN RTRIM(inv_unit_dtl_tbl.Description_vc) = 'Energy-Fixed Price Fixed Volume Off Peak' THEN 'Off Peak Contract Quantity'
        WHEN RTRIM(inv_unit_dtl_tbl.Description_vc) = 'Energy-Fixed Price Fixed Volume On Peak' THEN 'On Peak Contract Quantity'
        WHEN RTRIM(inv_unit_dtl_tbl.Description_vc) = 'Energy-Day Ahead Price On Peak' THEN
            CASE
                WHEN SUM(inv_unit_dtl_tbl.Volume_n) >= 0 THEN 'On Peak Over Usage'
                ELSE 'On Peak Under Usage'
            END
        WHEN RTRIM(inv_unit_dtl_tbl.Description_vc) = 'Energy-Day Ahead Price Off Peak' THEN
            CASE
                WHEN SUM(inv_unit_dtl_tbl.Volume_n) >= 0 THEN 'Off Peak Over Usage'
                ELSE 'Off Peak Under Usage'
            END
        ELSE RTRIM(inv_unit_dtl_tbl.Description_vc)
    END AS Description_vc


  • I've found turds similar to that in code written by consultants (who also managed to restart SQL Server in the middle of the day to enable remote DTC). Gotta love it when they don't even understand the basic usage of CASE.



  • The use of RTRIM() is a real smell to me. What...are people typing these things in? Then there's the use of SUM() in the middle of all of it. You just have to hope that this grew over time as a result of crazy scope creep (but then, doesn't everything?), and wasn't thought out ahead of time.



  • Doing a case on a description is ugly very fragile.  There should be a code/identifier in the table; you'd do a case on that.  And, I'd have a separate lookup table with a description, indexed by an identifier (the SUM bits cause a little complexity, but not much.  The lookup table has three columns; an identifier, a boolean (over/under usage) and a text description

    Then you get the description by using the inv_unit_dtl_tbl ID, and the sum calculation  (Sum(inv_unit_dtl_tbl.volumn_n) >= 0) as the key into the lookup table.

     




  •  @matthewr81 said:

    END END END END
    omg... this SQL has become self-sentient and self-WTF-aware. It litteraly begs for euthanasia.



  • Oh. I just noticed the naming scheme there. All the tables end in _tbl. That's so useful. God forbid you accidentally join on a constraint or named unique index instead of a table...



  • Should be done with a lookup table. Or at least nested in a function for readability. The database is the foundation of your application and if you don't start with good code there, you're sunk.



  • More than anything, I think the bigger WTF is using the description field as an identifier. What happens when someone says, "Let's change the description to 'Off Peak Energy-Fixed Price Fixed Volume'"? Or perhaps they start translating it to another language? Are they going to use localization in the SQL query itself?



  • @boomzilla said:

    The use of RTRIM() is a real smell to me. What...are people typing these things in?

    More likely, the field is a (n)char field, not a (n)varchar field. A lot of the tables in our system have keys that are char(10), so we have RTRIM()s scattered all over the place.



  • @blakeyrat said:

    Oh. I just noticed the naming scheme there. All the tables end in tbl. That's so useful. God forbid you accidentally join on a constraint or named unique index instead of a table...
     

    Had this sort of silliness at a past job — the table names all began with "TBL". The DBA told me that he had recommended this because he was concerned that people would get tables and views confused. Views, he said, should always start with "VW_".

    There were no views in the database.



  • I worked for a company which distinguished between regular "data" tables and tables which linked many-many constructs like this:

    tbl_user

    tbl_permissions

    lnk_user_permissions

    I think when we started using views we used view_



  • @jasmine2501 said:

    The database is the foundation of your application and if you don't start with good code there, you're sunk.

    Meh, our database has crappy code and design, but luckily the rest of our code... oh wait, nevermind.



  • @RHuckster said:

    I worked for a company which distinguished between regular "data" tables and tables which linked many-many constructs like this:

    tbl_user

    tbl_permissions

    lnk_user_permissions

    I think when we started using views we used view_

    I do many-many tables by combining the name of the two tables it joins.

    Session
    Segment
    SessionSegment

    Maybe that's stupid, but... not as stupid as the stuff I'm seeing in this thread.

    BTW, why does it matter that you be able to tell at a glance whether something is a Table or a View?



  • @blakeyrat said:

    BTW, why does it matter that you be able to tell at a glance whether something is a Table or a View?

    Have you ever tried to select top 10 from a stored procedure? It's embarassing...



  • @Sutherlands said:

    @blakeyrat said:

    BTW, why does it matter that you be able to tell at a glance whether something is a Table or a View?

    Have you ever tried to select top 10 from a stored procedure? It's embarassing...

    You mean a sproc that returns a @table? That works, yes?



  •  Totally agree with the _tbl, to be fair, that was not him, that was the original designer 15 years ago who did not know any better and being replaced (all of the fields have the type as well...)

     The format was exactly as I posted it as well... just formatting it better makes it easier to read, but wanted to capture the whole "OMG What is this?" feeling I got when I opened it.



  • @blakeyrat said:

    I do many-many tables by combining the name of the two tables it joins.

    Session
    Segment
    SessionSegment

    Maybe that's stupid, but... not as stupid as the stuff I'm seeing in this thread.

    I like to put underscores, since it makes it more obvious, not to mention easier to read, especially in a case insensitive environment like a SQL based DBMS.

    @blakeyrat said:

    BTW, why does it matter that you be able to tell at a glance whether something is a Table or a View?

    It can matter, at least as far as you might think twice about using a view if you don't really need it. But, yeah, that's pretty weak stuff once you incorporate the "at a glance" clause. We use T_ and V_ as a DBA enforced standard. Amusingly, stored procedures and functions and types have no such enforced standard scheme. Fortunately, the DBAs do not have the ability to cause atrocities such as iCount in the rest of our code.

    In fact, to circle back a bit, link tables in our DB tend to look like T_FOO_T_BAR, so it's really obvious what's going on there.



  • @blakeyrat said:

    @RHuckster said:

    I worked for a company which distinguished between regular "data" tables and tables which linked many-many constructs like this:

    tbl_user

    tbl_permissions

    lnk_user_permissions

    I think when we started using views we used view_

    I do many-many tables by combining the name of the two tables it joins.

    Session
    Segment
    SessionSegment

    Maybe that's stupid, but... not as stupid as the stuff I'm seeing in this thread.

     

    That's what we have at my current company. It's not stupid, at least not for simple relationships. It can get weird when you have link tables that need to be linked to other tables. In our case there was a link table that joined together three tables (let's say Widget, Stuff, and Thing) called WidgetStuffThing, and there came to be a need for a link table to join that link table to a second, separate instance of Thing. So now there's a link table called WidgetStuffThingThing, which defines the many-to-many relationship between WidgetStuffThing and Thing.



  • Link tables model relationships.  The name should thus be modelled after the meaning of the relationship, not after what is in the relationship.



  • @Someone You Know said:

    Had this sort of silliness at a past job — the table names all began with "TBL_". The DBA told me that he had recommended this because he was concerned that people would get tables and views confused. Views, he said, should always start with "VW_".

    There were no views in the database.

     

    Sounds like the coding standards manual I helped put together:

    Rule 43:  labels (such as are used in goto statements) are camelcase.

    Rule 43a:  never use goto statements.

     



  • @Someone You Know said:

    It can get weird when you have link tables that need to be linked to other tables. In our case there was a link table that joined together three tables (let's say Widget, Stuff, and Thing) called WidgetStuffThing, and there came to be a need for a link table to join that link table to a second, separate instance of Thing. So now there's a link table called WidgetStuffThingThing, which defines the many-to-many relationship between WidgetStuffThing and Thing.


    At that point, I usually start naming it more along the lines of the relationship that you've got. The mere combination of the tables is no longer terribly descriptive.



  • @Sutherlands said:

    Link tables model relationships.  The name should thus be modelled after the meaning of the relationship, not after what is in the relationship.

    Ok; so I have a Session table, each Session of which can be in zero or more Segments. What name would you choose for the many-many table?



  •  @Sutherlands said:

    Link tables model relationships.  The name should thus be modelled after the meaning of the relationship, not after what is in the relationship.

    That's reasonable, although in my experience, many DBs use the same link table for multiple types of relationships by using another column to distinguish them, ie:

    An artist might be associated with a genre, but some are genres the artist produces, while others are genres the artist merely appreciates. Thus, a table (artist_genre) may have the columns artist_id, genre_id, and type. One could make the case that you can (should?) break it into two separate tables, artist_genre_appreciates and artist_genre_produces, but I'm not sure what benefit that will buy you, besides maybe less index overhead.



  • @da Doctah said:

    @Someone You Know said:

    Had this sort of silliness at a past job — the table names all began with "TBL_". The DBA told me that he had recommended this because he was concerned that people would get tables and views confused. Views, he said, should always start with "VW_".

    There were no views in the database.

     

    Sounds like the coding standards manual I helped put together:

    Rule 43:  labels (such as are used in goto statements) are camelcase.

    Rule 43a:  never use goto statements.

     

    Yeah, although we didn't actually have a rule against views — it was just that the DBA and the developers I replaced hadn't used any, probably because they didn't really understand what they were.

    That DBA was a treasure trove of WTFs. We often had to hold his hand through the simplest things. The other developer I worked with once came back from a meeting with said DBA, sighed, shook his head, and said: "He's like a baby!"



  •  @blakeyrat said:

    @Sutherlands said:

    Link tables model relationships.  The name should thus be modelled after the meaning of the relationship, not after what is in the relationship.

    Ok; so I have a Session table, each Session of which can be in zero or more Segments. What name would you choose for the many-many table?

    Splonge ?

     



  • @RHuckster said:

    An artist might be associated with a genre, but some are genres the artist produces, while others are genres the artist merely appreciates. Thus, a table (artist_genre) may have the columns artist_id, genre_id, and type. One could make the case that you can (should?) break it into two separate tables, artist_genre_appreciates and artist_genre_produces, but I'm not sure what benefit that will buy you, besides maybe less index overhead.

    IMO, the best choice depends on how it's going to be used. If you're going to be doing a lot of queries to pull all the interests of a given artist, and you won't be doing a lot of "what artists have this interest" queries, then it's best to keep it all in one table. If you're in college or the application isn't for practical use, then it should be normalized - which in this case would, I think, actually involve 3 tables. The tricky part is you have to make the decision between going for academic-type good design and practical-type good design - those concepts are often in conflict.



  • @boomzilla said:

    @blakeyrat said:

    BTW, why does it matter that you be able to tell at a glance whether something is a Table or a View?

    It can matter, at least as far as you might think twice about using a view if you don't really need it. But, yeah, that's pretty weak stuff once you incorporate the "at a glance" clause. We use T_ and V_ as a DBA enforced standard. Amusingly, stored procedures and functions and types have no such enforced standard scheme. Fortunately, the DBAs do not have the ability to cause atrocities such as iCount in the rest of our code.

    In fact, to circle back a bit, link tables in our DB tend to look like T_FOO_T_BAR, so it's really obvious what's going on there.

     

    Tables and views and table-values functions should all have identical naming convention. As far as querying is concerned, they are all just "something that goes in a FROM clause". If you differentiate names based on object type then you get into trouble when you refactor a table into a view or function. You either have to have a view called tbl_Whatever or you have to change client code (defeating the purpose of refactoring on the database server).

    To quote Mr Celko, tibbling is "another silly convention among newbies…".

     

     



  • @havokk said:

    To quote Mr Celko, tibbling is "another silly convention among newbies…".

    "Tibbling?"



  • @jasmine2501 said:

    The tricky part is you have to make the decision between going for academic-type good design and practical-type good design - those concepts are often in conflict.
     

    This is true, and this is something I think many people don't understand. However, one thing about academic-type good design is (at least with normalization), it's purely objective reasoning, whereas defining if a database design is good practically-speaking is purely subjective reasoning, and depends on the requirements of the project at hand (which is therefore a point of contention between developers). As one of my colleagues often says, generally you want to start off "normalized" and worry about optimization when it really becomes worthwhile. You can bicker and argue over which denormalized strategy fits best for the project, but you'll never know where the real bottlenecks are and what truly needs optimization until you see exactly how your users actually play with the product. The old "YAGNI" principle comes into play here, where denormalization should be reactive, rather than preemptive, unless you know for sure the data in a certain denormalized state will benefit the project even before it launches.

    Granted, when you're talking about optimizing data by denormalizing or refactoring a populated database, it's a daunting task that requires a migration strategy and lots of tedious work to make sure it goes smoothly (nevermind any backwards compatibility you might need to take in account due to differing versions), but you already have to do that if you discover your original denormalized organization needs further optimization.



  • @havokk said:

    Tables and views and table-values functions should all have identical naming convention. As far as querying is concerned, they are all just "something that goes in a FROM clause". If you differentiate names based on object type then you get into trouble when you refactor a table into a view or function. You either have to have a view called tbl_Whatever or you have to change client code (defeating the purpose of refactoring on the database server).


    I think that systems Hungarian notation is retarded no matter where it shows up, but this sounds like a rather optimistic take on refactoring.



  • @blakeyrat said:

    BTW, why does it matter that you be able to tell at a glance whether something is a Table or a View?

    Makes it much easier to know where to look in SSMS schema browser, so you don't have to search through several dozen tables, only to find it's actually a view.



  • @toth said:

    @blakeyrat said:
    BTW, why does it matter that you be able to tell at a glance whether something is a Table or a View?

    Makes it much easier to know where to look in SSMS schema browser, so you don't have to search through several dozen tables, only to find it's actually a view.

     

    I've found it makes it much harder. If I want the Customers table, I expand the Tables branch, and at a glance can flick my eyes down to the chunk that starts with C.  Or I can even type "dbo.C" and jump to the right spot.

     When everything is prefixed with tbl_, this is significantly harder. I have to expand the Tables branch, and slowly read through chunks of entries. Sure, it's sorted alphabetically, but now the starting letter is hidden amongst a wall of text that looks alike.  And if I want to shortcut to it, I now have to type "dbo.tbl_C". I already hate having to type dbo., now I have to add an additional 4 characters (including a shift + key in the middle of nowhere + back again).

    And if I'm looking for a view instead of a table, which only happens a vast minority of the time, it's easier to search through two non-prefixed branches than it is to search through one prefixed branch. (Especially since, 50% of the time, I'll guess right anyways).

    Throw in Intellisense in SMSS 2008, which will hint you which are tables and which are views, and you have another reason to not bother with the tbl prefix.



  • @Lorne Kates said:

    Throw in Intellisense in SMSS 2008, which will hint you which are tables and which are views, and you have another reason to not bother with the tbl prefix.

    Which brings me to one of my biggest gripes about SSMS.  It's got Intellisense (a horribly broken version of it that's always screwing up what I type, but it's sort of got Intellisense) so why doesn't it have any browsing aids as part of the same system?

    In Delphi, I can CTRL-click on an identifier and it will take me to the place it's declared.  In Visual Studio, I can right-click on an identifier and say "find declaration" to accomplish the same thing. But where is the bloody "locate the definition of this table/column" command in SSMS?!?  Why in the world is it not there?

     



  • @toth said:

    @blakeyrat said:
    BTW, why does it matter that you be able to tell at a glance whether something is a Table or a View?

    Makes it much easier to know where to look in SSMS schema browser, so you don't have to search through several dozen tables, only to find it's actually a view.

    Ok, and when you find out it's a View, that information is important... why?

    You're seriously suggesting the only reason to give Views a little tag on their name is to make them easier to look up in a particular GUI tool any given user may or may not actually be using? That strikes you as a sensible and convincing reply to the question? Huh.



  • @Mason Wheeler said:

    Why in the world is it not there?
     

    Because SSMS is clicked together by retards. SSMS was a slapdash brute-force merger of Query Analyzer and Enterprise Manager. It lacks numerous very basic UI features. Some other things are much better, obviously.

     

    Don't get me started on the useless Ctrl+Tab abortion that it shares with Visual Studio.



  • @dhromed said:

    Because SSMS is clicked together by retards. SSMS was a slapdash brute-force merger of Query Analyzer and Enterprise Manager. It lacks numerous very basic UI features. Some other things are much better, obviously.

    And yet it's still better than any other GUI SQL management tool I've used by a long-shot. And not written in Java, so that's a plus.



  • @blakeyrat said:

    @Sutherlands said:

    Link tables model relationships.  The name should thus be modelled after the meaning of the relationship, not after what is in the relationship.

    Ok; so I have a Session table, each Session of which can be in zero or more Segments. What name would you choose for the many-many table?

    Well that depends... how the heck do they relate to each other?

    Change it to Car and Person, and it could be OwnedBy, RidesIn, PlansToBuy... but it's certainly not "CarPerson" (or at our workplace Car_mtm_Person)



  • @blakeyrat said:

    @dhromed said:
    Because SSMS is clicked together by retards. SSMS was a slapdash brute-force merger of Query Analyzer and Enterprise Manager. It lacks numerous very basic UI features. Some other things are much better, obviously.

    And yet it's still better than any other GUI SQL management tool I've used by a long-shot. And not written in Java, so that's a plus.

     

    Too true. MySQL Query Browser doesn't like it when you press the TAB button. Oh, and every now and then, it'll confuse ESC or CTRL-Z with "wipe out all the text in the current window without any way of recovering it". Lovely.

     And DreamCoder for Oracle (enough said?) lacks basic features like being able to easily view a table's schema-- or redirect query results to a CSV.

    I'll give Microsoft this: SMSS is lacking, but it's the best one I've used so far.

    However, any good karma they scrounged off SMSS got blown on AjaxToolKit...



  •  @Lorne Kates said:

    @blakeyrat said:

    @dhromed said:
    Because SSMS is clicked together by retards. SSMS was a slapdash brute-force merger of Query Analyzer and Enterprise Manager. It lacks numerous very basic UI features. Some other things are much better, obviously.

    And yet it's still better than any other GUI SQL management tool I've used by a long-shot. And not written in Java, so that's a plus.

     

    Too true. MySQL Query Browser doesn't like it when you press the TAB button. Oh, and every now and then, it'll confuse ESC or CTRL-Z with "wipe out all the text in the current window without any way of recovering it". Lovely.

     And DreamCoder for Oracle (enough said?) lacks basic features like being able to easily view a table's schema-- or redirect query results to a CSV.

    I'll give Microsoft this: SMSS is lacking, but it's the best one I've used so far.

    However, any good karma they scrounged off SMSS got blown on AjaxToolKit...

    Well, I haven't used their MS SQL tool, but I really like SQL Maestro Group's Firebird Maestro management tool for Firebird DBs.  Anyone know if MS SQL Maestro is any good?

     



  • @Billie said:

    @Jaime said:

    the nested cases seem to be unnecessary.
     

    Yeah, I would have done it like this; easier to read.

    span {
    font-family: 'Courier New';
    font-size: 10pt;
    color: #000000;
    }
    .sc0 {
    }
    .sc4 {
    color: #FF8000;
    }
    .sc5 {
    font-weight: bold;
    color: #0000FF;
    }
    .sc7 {
    color: #808080;
    }
    .sc10 {
    font-weight: bold;
    color: #000080;
    }
    .sc11 {
    }

    CASE
        WHEN RTRIM(inv_unit_dtl_tbl.Description_vc) = 'Energy-Fixed Price Fixed Volume Off Peak' THEN 'Off Peak Contract Quantity'
        WHEN RTRIM(inv_unit_dtl_tbl.Description_vc) = 'Energy-Fixed Price Fixed Volume On Peak' THEN 'On Peak Contract Quantity'
        WHEN RTRIM(inv_unit_dtl_tbl.Description_vc) = 'Energy-Day Ahead Price On Peak' THEN
            CASE
                WHEN SUM(inv_unit_dtl_tbl.Volume_n) >= 0 THEN 'On Peak Over Usage'
                ELSE 'On Peak Under Usage'
            END
        WHEN RTRIM(inv_unit_dtl_tbl.Description_vc) = 'Energy-Day Ahead Price Off Peak' THEN
            CASE
                WHEN SUM(inv_unit_dtl_tbl.Volume_n) >= 0 THEN 'Off Peak Over Usage'
                ELSE 'Off Peak Under Usage'
            END
        ELSE RTRIM(inv_unit_dtl_tbl.Description_vc)
    END AS Description_vc

     

    Right, this might be a troll, but it might not be, so:

     

    First iteration of improvement

     

    case RTRIM(inv_unit_dtl_tbl.Description_vc)
      when 'Energy-Fixed Price Fixed Volume Off Peak' THEN 'Off Peak Contract Quantity'
      when 'Energy-Fixed Price Fixed Volume On Peak' THEN 'On Peak Contract Quantity'
      when 'Energy-Day Ahead Price On Peak' THEN
                CASE WHEN SUM(inv_unit_dtl_tbl.Volume_n) >= 0
                  THEN 'On Peak Over Usage'
                  ELSE 'On Peak Under Usage' END
      WHEN 'Energy-Day Ahead Price Off Peak' THEN
                CASE WHEN SUM(inv_unit_dtl_tbl.Volume_n) >= 0
                  THEN 'Off Peak Over Usage'
                  ELSE 'Off Peak Under Usage' END
      ELSE RTRIM(inv_unit_dtl_tbl.Description_vc)
    end AS Description_vc

     

    Second iteration, replace inv_unit_dtl_tbl.Description_vc with a foreign key to a table like:

     

    create table InvUnitDescriptions (

      InvUnitDescriptionID int not null primary key,

      Description nvarchar(50) not null,

      DescriptionWhenNegative nvarchar(50)

    )

     

    and then just link to it and use

     

    case

     when SUM(inv_unit_dtl_tbl.Volume_n) >= 0

     then InvUnitDescription.Description

     else coalesce(InvUnitDescription.DescriptionWhenNegative, InvUnitDescription.Description)

    end

     

     



  • @emurphy said:

    case

     when SUM(inv_unit_dtl_tbl.Volume_n) >= 0

     then InvUnitDescription.Description

     else coalesce(InvUnitDescription.DescriptionWhenNegative, InvUnitDescription.Description)

    end

    Sounds like a happy one.

     


Log in to reply
 

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