Dynamic SQL in the wrong hands



  • So I had fun today... some changes were rolled out from our "offshore friends" to make our site skinnable. Working fine on staging environment, but when on live the site starts to run like a dog. A bit of investigation revealed where it was slowing down, and I tracked down this bit of code (ASP):

     Query = "select * from skinglobalsettings where skinid =" & Id    
            RS.Open Query,ObjConnection,1,1

            strColumnQuery="Select "
            strTableQuery=" from "
            strWhereQuery = " Where "
            strConditionQuery = ""
            Obj = 1

            Do While Not RS.EOF
            
                'Assign all column value to a array
                
                strColumnQuery = strColumnQuery & "a" & Obj & ".SettingValue as '" & RS("SettingName")& "', "
                
                strTableQuery = strTableQuery & "dbo.SkinGlobalSettings " & "a" & Obj & ", "
                
                strWhereQuery = strWhereQuery & "a" & Obj & ".SkinID = "
                
                strConditionQuery = strConditionQuery & "a" & Obj & ".SettingName = '" & RS("SettingName")& "' and "
                
                'Increment value
                
                
                Obj = Obj + 1
                
                strWhereQuery = strWhereQuery & "a" & Obj & ".SkinID and "
                
                
            RS.MoveNext
            Loop

            RS.Close()

                if Obj > 0 Then

                'Trim the last comma operator
                strColumnQuery = Mid(strColumnQuery,1,InStrRev(strColumnQuery,",")-1)
                strTableQuery = Mid(strTableQuery,1,InStrRev(strTableQuery,",")-1)
                strWhereQuery = Mid(strWhereQuery,1,InStrRev(strWhereQuery,"and")-1)
                strWhereQuery = Mid(strWhereQuery,1,InStrRev(strWhereQuery,"and")+3)
                
                'Concatenate all strings and form a dynamic query
                strQuery = strColumnQuery+strTableQuery+ strWhereQuery +strConditionQuery+ "a1"+".SkinId = " & Id  
                
            end if

            'Response.Write(strQuery)

            Set ObjRecordSet = Server.CreateObject ("ADODB.Recordset")
            ObjRecordSet.Open strQuery,ObjConnection,1,1

     I ran it through in my mind and gradually it dawned on me exactly what this code was doing. It was generating some dynamic SQL to essentially turn the rows into columns. You know, like most normal people would do with a PIVOT. But instead this is the query generated for the retrieved columns. Be warned, goggles do nothing and I suspect I will be scarred by life by this. It takes almost 30 seconds to run on our production server. I spent 5 minutes replacing the code with a PIVOT that returns the same thing instantly, gave it to the offshore people and suggested they never send me code that can dynamically create 117 joins again...

     Select a1.SettingValue as 'ApplicationBGColor', a2.SettingValue as 'ApplicationFontColor', a3.SettingValue as 'ApplicationFontName', a4.SettingValue as 'ApplicationFontSize', a5.SettingValue as 'ApplicationFontWeight', a6.SettingValue as 'ApplicationWarningBorderColor', a7.SettingValue as 'ApplicationWarningTextBG', a8.SettingValue as 'ApplicationWarningTextColor', a9.SettingValue as 'BulletColor', a10.SettingValue as 'ButtonBGColor', a11.SettingValue as 'ButtonTextColor', a12.SettingValue as 'CustomerURLImage', a13.SettingValue as 'DateControlBGColor', a14.SettingValue as 'DateControlDaySelectColor', a15.SettingValue as 'DateControlTheme', a16.SettingValue as 'DeutschContactNumber', a17.SettingValue as 'DeutschCopyRightsText', a18.SettingValue as 'DeutschSiteName', a19.SettingValue as 'DropDownBGColor', a20.SettingValue as 'DropDownTextColor', a21.SettingValue as 'EnglishContactNumber', a22.SettingValue as 'EnglishCopyRightsText', a23.SettingValue as 'EnglishSiteName', a24.SettingValue as 'GridContentBGColor', a25.SettingValue as 'GridContentTextColor', a26.SettingValue as 'GridFontName', a27.SettingValue as 'GridFontSize', a28.SettingValue as 'GridHeaderBGColor', a29.SettingValue as 'GridHeaderTextColor', a30.SettingValue as 'GridOuterBoxColor', a31.SettingValue as 'GridOuterBoxTextColor', a32.SettingValue as 'GridRowOverColor', a33.SettingValue as 'GridRowSelectColor', a34.SettingValue as 'HelpBGColor', a35.SettingValue as 'HelpHorizontalLineColor', a36.SettingValue as 'HelpHyperLinkMouseOverColor', a37.SettingValue as 'HelpHyperLinkTextColor', a38.SettingValue as 'HelpSpecialNotesColor', a39.SettingValue as 'HelpTableBGColor', a40.SettingValue as 'HelpTableHeaderColor', a41.SettingValue as 'HelpTableHeaderTextColor', a42.SettingValue as 'HelpTextColor', a43.SettingValue as 'HorizontalLineColor', a44.SettingValue as 'HyperLinkMouseOverColor', a45.SettingValue as 'HyperLinkTextColor', a46.SettingValue as 'LabelTextColor', a47.SettingValue as 'ListBoxBGColor', a48.SettingValue as 'ListBoxTextColor', a49.SettingValue as 'LoginPageBGColor', a50.SettingValue as 'LoginPageImage1', a51.SettingValue as 'LoginPageImage2', a52.SettingValue as 'LoginPageImage3', a53.SettingValue as 'MainFormBGColor', a54.SettingValue as 'MainFormBorderColor', a55.SettingValue as 'MainFormHeaderTextColor', a56.SettingValue as 'MainFormTextColor', a57.SettingValue as 'MainMenuBGColor', a58.SettingValue as 'MainMenuMouseOverColor', a59.SettingValue as 'MainMenuTextColor', a60.SettingValue as 'MandatoryFieldBGColor', a61.SettingValue as 'MandatoryFieldTextColor', a62.SettingValue as 'PageHeaderImage', a63.SettingValue as 'ReadOnlyFieldBGColor', a64.SettingValue as 'ReadOnlyFieldTextColor', a65.SettingValue as 'ReportBGColor', a66.SettingValue as 'ReportColumnContentFontSize', a67.SettingValue as 'ReportCopyRightFontSize', a68.SettingValue as 'ReportDisclaimerFontColor', a69.SettingValue as 'ReportDisclaimerFontSize', a70.SettingValue as 'ReportDottedLineColor', a71.SettingValue as 'ReportFontColor', a72.SettingValue as 'ReportFontName', a73.SettingValue as 'ReportFontSize', a74.SettingValue as 'ReportFontWeight', a75.SettingValue as 'ReportHeaderFontSize', a76.SettingValue as 'ReportHeaderTextColor', a77.SettingValue as 'ReportHyperLinkMouseOverColor', a78.SettingValue as 'ReportHyperLinkTextColor', a79.SettingValue as 'ReportSubHeaderFontSize', a80.SettingValue as 'ReportSubHeaderTextColor', a81.SettingValue as 'ReportTitleFontSize', a82.SettingValue as 'ReportURLImage', a83.SettingValue as 'ReportVehicleArrivedColor', a84.SettingValue as 'ReportVehicleContinuedColor', a85.SettingValue as 'ReportVehicleDepartedColor', a86.SettingValue as 'ReportVehicleIdledColor', a87.SettingValue as 'ReportVehicleStartedColor', a88.SettingValue as 'ReportVehicleStoppedColor', a89.SettingValue as 'ReportVehicleTurnedColor', a90.SettingValue as 'ReportVehicleTurnedLeftColor', a91.SettingValue as 'ReportVehicleTurnedRightColor', a92.SettingValue as 'ReportVehicleViaColor', a93.SettingValue as 'ReportWarningFontColor', a94.SettingValue as 'ReportWarningFontSize', a95.SettingValue as 'ScrollBarArrowColor', a96.SettingValue as 'ScrollBarBaceColor', a97.SettingValue as 'ScrollBarDarkShadowColor', a98.SettingValue as 'ScrollBarFaceColor', a99.SettingValue as 'SetupMenuBGColor', a100.SettingValue as 'SetupMenuTextColor', a101.SettingValue as 'SubFormBGColor', a102.SettingValue as 'SubFormBorderColor', a103.SettingValue as 'SubFormTextColor', a104.SettingValue as 'SubMenuBorderColor', a105.SettingValue as 'SvenskaContactNumber', a106.SettingValue as 'SvenskaCopyRightsText', a107.SettingValue as 'SvenskaSiteName', a108.SettingValue as 'TabBGColor', a109.SettingValue as 'TabHeaderTextColor', a110.SettingValue as 'TextBoxBGColor', a111.SettingValue as 'TextBoxTextColor', a112.SettingValue as 'TreeviewBGColor', a113.SettingValue as 'TreeviewRowOverColor', a114.SettingValue as 'TreeviewRowOverTextColor', a115.SettingValue as 'TreeviewRowSelection', a116.SettingValue as 'TreeviewTextColor', a117.SettingValue as 'VerticalLineColor' from dbo.SkinGlobalSettings a1, dbo.SkinGlobalSettings a2, dbo.SkinGlobalSettings a3, dbo.SkinGlobalSettings a4, dbo.SkinGlobalSettings a5, dbo.SkinGlobalSettings a6, dbo.SkinGlobalSettings a7, dbo.SkinGlobalSettings a8, dbo.SkinGlobalSettings a9, dbo.SkinGlobalSettings a10, dbo.SkinGlobalSettings a11, dbo.SkinGlobalSettings a12, dbo.SkinGlobalSettings a13, dbo.SkinGlobalSettings a14, dbo.SkinGlobalSettings a15, dbo.SkinGlobalSettings a16, dbo.SkinGlobalSettings a17, dbo.SkinGlobalSettings a18, dbo.SkinGlobalSettings a19, dbo.SkinGlobalSettings a20, dbo.SkinGlobalSettings a21, dbo.SkinGlobalSettings a22, dbo.SkinGlobalSettings a23, dbo.SkinGlobalSettings a24, dbo.SkinGlobalSettings a25, dbo.SkinGlobalSettings a26, dbo.SkinGlobalSettings a27, dbo.SkinGlobalSettings a28, dbo.SkinGlobalSettings a29, dbo.SkinGlobalSettings a30, dbo.SkinGlobalSettings a31, dbo.SkinGlobalSettings a32, dbo.SkinGlobalSettings a33, dbo.SkinGlobalSettings a34, dbo.SkinGlobalSettings a35, dbo.SkinGlobalSettings a36, dbo.SkinGlobalSettings a37, dbo.SkinGlobalSettings a38, dbo.SkinGlobalSettings a39, dbo.SkinGlobalSettings a40, dbo.SkinGlobalSettings a41, dbo.SkinGlobalSettings a42, dbo.SkinGlobalSettings a43, dbo.SkinGlobalSettings a44, dbo.SkinGlobalSettings a45, dbo.SkinGlobalSettings a46, dbo.SkinGlobalSettings a47, dbo.SkinGlobalSettings a48, dbo.SkinGlobalSettings a49, dbo.SkinGlobalSettings a50, dbo.SkinGlobalSettings a51, dbo.SkinGlobalSettings a52, dbo.SkinGlobalSettings a53, dbo.SkinGlobalSettings a54, dbo.SkinGlobalSettings a55, dbo.SkinGlobalSettings a56, dbo.SkinGlobalSettings a57, dbo.SkinGlobalSettings a58, dbo.SkinGlobalSettings a59, dbo.SkinGlobalSettings a60, dbo.SkinGlobalSettings a61, dbo.SkinGlobalSettings a62, dbo.SkinGlobalSettings a63, dbo.SkinGlobalSettings a64, dbo.SkinGlobalSettings a65, dbo.SkinGlobalSettings a66, dbo.SkinGlobalSettings a67, dbo.SkinGlobalSettings a68, dbo.SkinGlobalSettings a69, dbo.SkinGlobalSettings a70, dbo.SkinGlobalSettings a71, dbo.SkinGlobalSettings a72, dbo.SkinGlobalSettings a73, dbo.SkinGlobalSettings a74, dbo.SkinGlobalSettings a75, dbo.SkinGlobalSettings a76, dbo.SkinGlobalSettings a77, dbo.SkinGlobalSettings a78, dbo.SkinGlobalSettings a79, dbo.SkinGlobalSettings a80, dbo.SkinGlobalSettings a81, dbo.SkinGlobalSettings a82, dbo.SkinGlobalSettings a83, dbo.SkinGlobalSettings a84, dbo.SkinGlobalSettings a85, dbo.SkinGlobalSettings a86, dbo.SkinGlobalSettings a87, dbo.SkinGlobalSettings a88, dbo.SkinGlobalSettings a89, dbo.SkinGlobalSettings a90, dbo.SkinGlobalSettings a91, dbo.SkinGlobalSettings a92, dbo.SkinGlobalSettings a93, dbo.SkinGlobalSettings a94, dbo.SkinGlobalSettings a95, dbo.SkinGlobalSettings a96, dbo.SkinGlobalSettings a97, dbo.SkinGlobalSettings a98, dbo.SkinGlobalSettings a99, dbo.SkinGlobalSettings a100, dbo.SkinGlobalSettings a101, dbo.SkinGlobalSettings a102, dbo.SkinGlobalSettings a103, dbo.SkinGlobalSettings a104, dbo.SkinGlobalSettings a105, dbo.SkinGlobalSettings a106, dbo.SkinGlobalSettings a107, dbo.SkinGlobalSettings a108, dbo.SkinGlobalSettings a109, dbo.SkinGlobalSettings a110, dbo.SkinGlobalSettings a111, dbo.SkinGlobalSettings a112, dbo.SkinGlobalSettings a113, dbo.SkinGlobalSettings a114, dbo.SkinGlobalSettings a115, dbo.SkinGlobalSettings a116, dbo.SkinGlobalSettings a117 Where a1.SkinID = a2.SkinID and a2.SkinID = a3.SkinID and a3.SkinID = a4.SkinID and a4.SkinID = a5.SkinID and a5.SkinID = a6.SkinID and a6.SkinID = a7.SkinID and a7.SkinID = a8.SkinID and a8.SkinID = a9.SkinID and a9.SkinID = a10.SkinID and a10.SkinID = a11.SkinID and a11.SkinID = a12.SkinID and a12.SkinID = a13.SkinID and a13.SkinID = a14.SkinID and a14.SkinID = a15.SkinID and a15.SkinID = a16.SkinID and a16.SkinID = a17.SkinID and a17.SkinID = a18.SkinID and a18.SkinID = a19.SkinID and a19.SkinID = a20.SkinID and a20.SkinID = a21.SkinID and a21.SkinID = a22.SkinID and a22.SkinID = a23.SkinID and a23.SkinID = a24.SkinID and a24.SkinID = a25.SkinID and a25.SkinID = a26.SkinID and a26.SkinID = a27.SkinID and a27.SkinID = a28.SkinID and a28.SkinID = a29.SkinID and a29.SkinID = a30.SkinID and a30.SkinID = a31.SkinID and a31.SkinID = a32.SkinID and a32.SkinID = a33.SkinID and a33.SkinID = a34.SkinID and a34.SkinID = a35.SkinID and a35.SkinID = a36.SkinID and a36.SkinID = a37.SkinID and a37.SkinID = a38.SkinID and a38.SkinID = a39.SkinID and a39.SkinID = a40.SkinID and a40.SkinID = a41.SkinID and a41.SkinID = a42.SkinID and a42.SkinID = a43.SkinID and a43.SkinID = a44.SkinID and a44.SkinID = a45.SkinID and a45.SkinID = a46.SkinID and a46.SkinID = a47.SkinID and a47.SkinID = a48.SkinID and a48.SkinID = a49.SkinID and a49.SkinID = a50.SkinID and a50.SkinID = a51.SkinID and a51.SkinID = a52.SkinID and a52.SkinID = a53.SkinID and a53.SkinID = a54.SkinID and a54.SkinID = a55.SkinID and a55.SkinID = a56.SkinID and a56.SkinID = a57.SkinID and a57.SkinID = a58.SkinID and a58.SkinID = a59.SkinID and a59.SkinID = a60.SkinID and a60.SkinID = a61.SkinID and a61.SkinID = a62.SkinID and a62.SkinID = a63.SkinID and a63.SkinID = a64.SkinID and a64.SkinID = a65.SkinID and a65.SkinID = a66.SkinID and a66.SkinID = a67.SkinID and a67.SkinID = a68.SkinID and a68.SkinID = a69.SkinID and a69.SkinID = a70.SkinID and a70.SkinID = a71.SkinID and a71.SkinID = a72.SkinID and a72.SkinID = a73.SkinID and a73.SkinID = a74.SkinID and a74.SkinID = a75.SkinID and a75.SkinID = a76.SkinID and a76.SkinID = a77.SkinID and a77.SkinID = a78.SkinID and a78.SkinID = a79.SkinID and a79.SkinID = a80.SkinID and a80.SkinID = a81.SkinID and a81.SkinID = a82.SkinID and a82.SkinID = a83.SkinID and a83.SkinID = a84.SkinID and a84.SkinID = a85.SkinID and a85.SkinID = a86.SkinID and a86.SkinID = a87.SkinID and a87.SkinID = a88.SkinID and a88.SkinID = a89.SkinID and a89.SkinID = a90.SkinID and a90.SkinID = a91.SkinID and a91.SkinID = a92.SkinID and a92.SkinID = a93.SkinID and a93.SkinID = a94.SkinID and a94.SkinID = a95.SkinID and a95.SkinID = a96.SkinID and a96.SkinID = a97.SkinID and a97.SkinID = a98.SkinID and a98.SkinID = a99.SkinID and a99.SkinID = a100.SkinID and a100.SkinID = a101.SkinID and a101.SkinID = a102.SkinID and a102.SkinID = a103.SkinID and a103.SkinID = a104.SkinID and a104.SkinID = a105.SkinID and a105.SkinID = a106.SkinID and a106.SkinID = a107.SkinID and a107.SkinID = a108.SkinID and a108.SkinID = a109.SkinID and a109.SkinID = a110.SkinID and a110.SkinID = a111.SkinID and a111.SkinID = a112.SkinID and a112.SkinID = a113.SkinID and a113.SkinID = a114.SkinID and a114.SkinID = a115.SkinID and a115.SkinID = a116.SkinID and a116.SkinID = a117.SkinID and a1.SettingName = 'ApplicationBGColor' and a2.SettingName = 'ApplicationFontColor' and a3.SettingName = 'ApplicationFontName' and a4.SettingName = 'ApplicationFontSize' and a5.SettingName = 'ApplicationFontWeight' and a6.SettingName = 'ApplicationWarningBorderColor' and a7.SettingName = 'ApplicationWarningTextBG' and a8.SettingName = 'ApplicationWarningTextColor' and a9.SettingName = 'BulletColor' and a10.SettingName = 'ButtonBGColor' and a11.SettingName = 'ButtonTextColor' and a12.SettingName = 'CustomerURLImage' and a13.SettingName = 'DateControlBGColor' and a14.SettingName = 'DateControlDaySelectColor' and a15.SettingName = 'DateControlTheme' and a16.SettingName = 'DeutschContactNumber' and a17.SettingName = 'DeutschCopyRightsText' and a18.SettingName = 'DeutschSiteName' and a19.SettingName = 'DropDownBGColor' and a20.SettingName = 'DropDownTextColor' and a21.SettingName = 'EnglishContactNumber' and a22.SettingName = 'EnglishCopyRightsText' and a23.SettingName = 'EnglishSiteName' and a24.SettingName = 'GridContentBGColor' and a25.SettingName = 'GridContentTextColor' and a26.SettingName = 'GridFontName' and a27.SettingName = 'GridFontSize' and a28.SettingName = 'GridHeaderBGColor' and a29.SettingName = 'GridHeaderTextColor' and a30.SettingName = 'GridOuterBoxColor' and a31.SettingName = 'GridOuterBoxTextColor' and a32.SettingName = 'GridRowOverColor' and a33.SettingName = 'GridRowSelectColor' and a34.SettingName = 'HelpBGColor' and a35.SettingName = 'HelpHorizontalLineColor' and a36.SettingName = 'HelpHyperLinkMouseOverColor' and a37.SettingName = 'HelpHyperLinkTextColor' and a38.SettingName = 'HelpSpecialNotesColor' and a39.SettingName = 'HelpTableBGColor' and a40.SettingName = 'HelpTableHeaderColor' and a41.SettingName = 'HelpTableHeaderTextColor' and a42.SettingName = 'HelpTextColor' and a43.SettingName = 'HorizontalLineColor' and a44.SettingName = 'HyperLinkMouseOverColor' and a45.SettingName = 'HyperLinkTextColor' and a46.SettingName = 'LabelTextColor' and a47.SettingName = 'ListBoxBGColor' and a48.SettingName = 'ListBoxTextColor' and a49.SettingName = 'LoginPageBGColor' and a50.SettingName = 'LoginPageImage1' and a51.SettingName = 'LoginPageImage2' and a52.SettingName = 'LoginPageImage3' and a53.SettingName = 'MainFormBGColor' and a54.SettingName = 'MainFormBorderColor' and a55.SettingName = 'MainFormHeaderTextColor' and a56.SettingName = 'MainFormTextColor' and a57.SettingName = 'MainMenuBGColor' and a58.SettingName = 'MainMenuMouseOverColor' and a59.SettingName = 'MainMenuTextColor' and a60.SettingName = 'MandatoryFieldBGColor' and a61.SettingName = 'MandatoryFieldTextColor' and a62.SettingName = 'PageHeaderImage' and a63.SettingName = 'ReadOnlyFieldBGColor' and a64.SettingName = 'ReadOnlyFieldTextColor' and a65.SettingName = 'ReportBGColor' and a66.SettingName = 'ReportColumnContentFontSize' and a67.SettingName = 'ReportCopyRightFontSize' and a68.SettingName = 'ReportDisclaimerFontColor' and a69.SettingName = 'ReportDisclaimerFontSize' and a70.SettingName = 'ReportDottedLineColor' and a71.SettingName = 'ReportFontColor' and a72.SettingName = 'ReportFontName' and a73.SettingName = 'ReportFontSize' and a74.SettingName = 'ReportFontWeight' and a75.SettingName = 'ReportHeaderFontSize' and a76.SettingName = 'ReportHeaderTextColor' and a77.SettingName = 'ReportHyperLinkMouseOverColor' and a78.SettingName = 'ReportHyperLinkTextColor' and a79.SettingName = 'ReportSubHeaderFontSize' and a80.SettingName = 'ReportSubHeaderTextColor' and a81.SettingName = 'ReportTitleFontSize' and a82.SettingName = 'ReportURLImage' and a83.SettingName = 'ReportVehicleArrivedColor' and a84.SettingName = 'ReportVehicleContinuedColor' and a85.SettingName = 'ReportVehicleDepartedColor' and a86.SettingName = 'ReportVehicleIdledColor' and a87.SettingName = 'ReportVehicleStartedColor' and a88.SettingName = 'ReportVehicleStoppedColor' and a89.SettingName = 'ReportVehicleTurnedColor' and a90.SettingName = 'ReportVehicleTurnedLeftColor' and a91.SettingName = 'ReportVehicleTurnedRightColor' and a92.SettingName = 'ReportVehicleViaColor' and a93.SettingName = 'ReportWarningFontColor' and a94.SettingName = 'ReportWarningFontSize' and a95.SettingName = 'ScrollBarArrowColor' and a96.SettingName = 'ScrollBarBaceColor' and a97.SettingName = 'ScrollBarDarkShadowColor' and a98.SettingName = 'ScrollBarFaceColor' and a99.SettingName = 'SetupMenuBGColor' and a100.SettingName = 'SetupMenuTextColor' and a101.SettingName = 'SubFormBGColor' and a102.SettingName = 'SubFormBorderColor' and a103.SettingName = 'SubFormTextColor' and a104.SettingName = 'SubMenuBorderColor' and a105.SettingName = 'SvenskaContactNumber' and a106.SettingName = 'SvenskaCopyRightsText' and a107.SettingName = 'SvenskaSiteName' and a108.SettingName = 'TabBGColor' and a109.SettingName = 'TabHeaderTextColor' and a110.SettingName = 'TextBoxBGColor' and a111.SettingName = 'TextBoxTextColor' and a112.SettingName = 'TreeviewBGColor' and a113.SettingName = 'TreeviewRowOverColor' and a114.SettingName = 'TreeviewRowOverTextColor' and a115.SettingName = 'TreeviewRowSelection' and a116.SettingName = 'TreeviewTextColor' and a117.SettingName = 'VerticalLineColor' and a1.SkinId = 0



  • Great, my head already feels like a used football; after seeing this I'm now bleeding from my ears.



  • "I'm every nightmare you've ever had. I'm your worst dream come true. I'm everything you ever were afraid of."



  • You're just lucky this is SQL Server... In Oracle prior to 11g there is no PIVOT; hence this would be the way to build a SQL pivot table (or use 100+ case statements). From Oracle 10g or lower you're better off just querying it flat and building the pivot in arrays when reading the recordset

     Edit - though in Oracle 10 (and 8,9 i think) you could use WITH to make it a lot cleaner and somewhat faster.



  • @valerion said:

    DeutschContactNumber ... EnglishContactNumber

    What happens when you need to add French? FrançaisContactNumber?

    *scrolls down*

    @valerion said:

    SvenskaContactNumber

    Never mind I asked...



  •  Yeah don't even go there.... another nice WTF of the site is that there are separate XSLs for each language. All done before my time I hasten to add.

     If we ever do have to add another language........... I keep my life happy but not thinking about that scenario.



  •  huh



  • This reminds me of a while back when I posted about this massive 1500+ line stored procedure whose sole purpose was to generate and even larger stored procedure.


Log in to reply
 

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