The SQL Query From Hell or... the Horror, the horror.



  • As a contractor I see a lot of very bad code - but nothing on earth could prepare me for this monstrosity. This was originally running on an Access DB and they wanted me to upsize it to SQLServer. I was brought in to figure out how to make it run faster than a couple of hours...

    Note the nifty 30 plus level nesting!

    Feast your eyes on the embedded string parsing!

    As Zappa once said: 'The torture never stops'

    >>>>>>>>>>>>>>>>>>>>> BEGIN >>>>>>>>>>>>>>>>>>>>>>>>

      Select [OrderDate], [TDetail].[JobNo], [StyleNo], [CusStyleNo], [TheirStyle], [GoldKT], [RingSize], [Description], [PONo], [CustomerID], [ReqExportDate], [TotalPcs], [Export], [RY], [RN], [CW], [WS], [CA], [GC], [GD], [ST], [PL], [RH], [PK], [FDate], [MinSDate], [MaxSDate], [Remark], [Dia], [Stn], [Component], [Diamond], [Stone], [Acc], [Mold], [CWRecDate], [MCWRecDate] From
               ((((((((((((((((Select [OrderDate], [TOrder].[JobNo], [StyleNo], [CusStyleNo], [TheirStyle], [GoldKT], [RingSize], [Description], [PONo], [CustomerID], [ReqExportDate], [TotalPcs], [SQty3] As Export, [CDia] As Diamond, [CStone] As Stone, [CComponent] As Acc, [TMold].[JobNo] As Mold, [CWRecDate], [MCWRecDate] From
                (((((((((Select [OrderDate], [JobNo], [StyleNo], Left([StyleNo],6) As ST6, [CusStyleNo], [TheirStyle], [GoldKT], [RingSize], [Description], [PONo], [CustomerID], [ReqExportDate], [TotalPcs] From
                (Select * From [ConfirmOrderDetail] Where Status='C') AS TOrderG Where [OrderDate] >= '02/21/2004') As TOrder
                LEFT JOIN (Select [ConfirmStone].[JobNo], Count([ConfirmStone].[JobNo]) As CDia From [ConfirmStone] LEFT JOIN [ConfirmOrderDetail] ON [ConfirmStone].[JobNo]=[ConfirmOrderDetail].[JobNo] Where [Status]='C' and CDate(Format([OrderDate],'Short Date')) >= #02/21/2004# and [Stone] Like 'DIA' " & IIf(con1$ <> "", "and [ConfirmStone].[JobNo] Like '" & txtJobNo.Text & "' ", "") & con2$ & con3$ & con4$ & con5$ & con6$ & con7$ & " Group By [ConfirmStone].[JobNo]) As TCDia ON [TOrder].[JobNo]=[TCDia].[JobNo])
                LEFT JOIN (Select [ConfirmStone].[JobNo], Count([ConfirmStone].[JobNo]) As CStone From [ConfirmStone] LEFT JOIN [ConfirmOrderDetail] ON [ConfirmStone].[JobNo]=[ConfirmOrderDetail].[JobNo] Where [Status]='C' and CDate(Format([OrderDate],'Short Date')) >= #02/21/2004# and Not([Stone] Like 'DIA' or [Stone] Like '%SEMI%') " & IIf(con1$ <> "", "and [ConfirmStone].[JobNo] Like '" & txtJobNo.Text & "' ", "") & con2$ & con3$ & con4$ & con5$ & con6$ & con7$ & " Group By [ConfirmStone].[JobNo]) As TCStone ON [TOrder].[JobNo]=[TCStone].[JobNo])
                LEFT JOIN (Select [ConfirmComponent].[JobNo], Count([ConfirmComponent].[JobNo]) As CComponent From [ConfirmComponent] LEFT JOIN [ConfirmOrderDetail] ON [ConfirmComponent].[JobNo]=[ConfirmOrderDetail].[JobNo] Where [Status]='C' and CDate(Format([OrderDate],'Short Date')) >= #02/21/2004# and Not(([CusComponentNo] Is Null or [CusComponentNo]='') and ([Type] Is Null or [Type]='') and ([ConfirmComponent].[GoldKT] Is Null or [ConfirmComponent].[GoldKT]='') and [Qty]=0) " & IIf(con1$ <> "", "and [ConfirmComponent].[JobNo] Like '" & txtJobNo.Text & "' ", "") & con2$ & con3$ & con4$ & con5$ & con6$ & con7$ & " Group By [ConfirmComponent].[JobNo]) As TCComponent ON [TOrder].[JobNo]=[TCComponent].[JobNo])
                LEFT JOIN (Select [JobNo], [CWRecDate] From [MasterPlanMold] Where [JobNo] Like '%' " & con1 & con2 & con13$ & "Group By [JobNo], [CWRecDate]) As TMold ON [TOrder].[JobNo]=[TMold].[JobNo])
                LEFT JOIN (Select Left([MasterPlanMold].[StyleNo],6) As ST6, [CWRecDate] As MCWRecDate, [RecBOMDate] From [MasterPlanMold]
                INNER JOIN (Select Left([StyleNo],6) As ST6, Max([RecBOMDate]) As MaxBOM From [MasterPlanMold] Where [JobNo] Like '%' " & con13$ & "Group By Left([StyleNo],6)) As TMaxBOM
                ON Left([MasterPlanMold].[StyleNo],6)=[TMaxBOM].[ST6] and [MasterPlanMold].[RecBOMDate]=[TMaxBOM].[MaxBOM]
                Group BY Left([MasterPlanMold].[StyleNo],6), [CWRecDate], [RecBOMDate]) As TMoldSty ON [TOrder].[ST6]=[TMoldSty].[ST6])
                LEFT JOIN (Select [JobNo], Sum([Qty]) AS SQty1 From [InvoiceDetail] LEFT JOIN [InvoiceMaster] ON [InvoiceDetail].[InvoiceNo]=[InvoiceMaster].[InvoiceNo] Where [Date]<#" & gfnCVDate(Date) & "# and [JobNo] Like '%' " & con1$ & con2$ & con3$ & con4$ & " Group By [JobNo]) As TInvoice ON [TOrder].[JobNo]=[TInvoice].[JobNo])
                LEFT JOIN (Select [JobNo], Sum([Qty]) AS SQty2 From [Invoice2Detail] LEFT JOIN [Invoice2Master] ON [Invoice2Detail].[InvoiceNo]=[Invoice2Master].[InvoiceNo] Where [Date]<#" & gfnCVDate(Date) & "# and [JobNo] Like '%' " & con1$ & con2$ & con3$ & con4$ & " Group By [JobNo]) As TInvoice2 ON [TOrder].[JobNo]=[TInvoice2].[JobNo])
                LEFT JOIN (Select [JobNo], Sum([Qty]) AS SQty3 From [IN] Where [JobNo] Like '%' " & con1$ & con2$ & con4$ & " Group By [JobNo]) As TFinish ON [TOrder].[JobNo]=[TFinish].[JobNo])
                Where [TotalPcs] >(IIf(Isnull([SQty1]),0,[SQty1])+IIf(Isnull([SQty2]),0,[SQty2])+IIf(Isnull([SQty3]),0,[SQty3])) " & con9$ & con11$ & con14$ & ") As TDetail
                LEFT JOIN (Select [JobNo], Min([FacDate]) As FDate, Min([ShipDate]) As MinSDate, Max([ShipDate]) As MaxSDate From [MasterPlan] Where [JobNo] Like '%' " & con1$ & con2$ & "Group By [JobNo]) As TMasterPlan ON [TDetail].[JobNo]=[TMasterPlan].[JobNo])
                    LEFT JOIN (Select [LotNo] As JobNo, Sum([Qty]) As RY From [Remake] Where [Remake]='Y' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "' ", "") & con2$ & con3$ & "Group By [LotNo]) As TRemakeY ON [TDetail].[JobNo]= [TRemakeY].[JobNo])
                    LEFT JOIN (Select [LotNo] As JobNo, Sum([Qty]) As RN From [Remake] Where [Remake]='N' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "' ", "") & con2$ & con3$ & "Group By [LotNo]) As TRemakeN ON [TDetail].[JobNo]= [TRemakeN].[JobNo])
       
                    LEFT JOIN (Select [TUNIONCW].[JobNo], (IIf(Isnull([OCW]),0,[OCW])-IIf(Isnull([CWQty]),0,[CWQty])+IIf(Isnull([CWAdjust]),0,[CWAdjust])) As CW From
                    ((((Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo From [DeptFinish] Where ([Dept_ID]='04' or [To]='CW') " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))
                    UNION Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo From [Adjust] Where [Dept_ID]='04' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TUNIONCW
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As OCW From [DeptFinish] Where [To]='CW' and Not([To] Is Null) " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TOCW ON [TUNIONCW].[JobNo]=[TOCW].[JobNo])
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As CWQty From [DeptFinish] Where [Dept_ID]='04' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TCWQty ON [TUNIONCW].[JobNo]=[TCWQty].[JobNo])
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As CWAdjust From [Adjust] Where [Dept_ID]='04' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TCWAdjustQty ON [TUNIONCW].[JobNo]=[TCWAdjustQty].[JobNo])) As TCW ON [TDetail].[JobNo]= [TCW].[JobNo])
       
          LEFT JOIN (Select [TUNIONWS].[JobNo], (IIf(Isnull([OWS]),0,[OWS])-IIf(Isnull([WSQty]),0,[WSQty])+IIf(Isnull([WSAdjust]),0,[WSAdjust])) As WS From " _
                    ((((Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo From [DeptFinish] Where ([Dept_ID]='05' or [To]='WS') " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) " _
                    UNION Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo From [Adjust] Where [Dept_ID]='05' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TUNIONWS " _
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As OWS From [DeptFinish] Where [To]='WS' and Not([To] Is Null) " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TOWS ON [TUNIONWS].[JobNo]=[TOWS].[JobNo]) " _
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As WSQty From [DeptFinish] Where [Dept_ID]='05' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TWSQty ON [TUNIONWS].[JobNo]=[TWSQty].[JobNo]) " _
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As WSAdjust From [Adjust] Where [Dept_ID]='05' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TWSAdjustQty ON [TUNIONWS].[JobNo]=[TWSAdjustQty].[JobNo]) " _
                    Where IIf(Isnull([OWS]),0,[OWS])-IIf(Isnull([WSQty]),0,[WSQty])+IIf(Isnull([WSAdjust]),0,[WSAdjust]) <>0) As TWS ON [TDetail].[JobNo]= [TWS].[JobNo])
       
        LEFT JOIN (Select [TUNIONCA].[JobNo], (IIf(Isnull([OCA]),0,[OCA])-IIf(Isnull([CAQty]),0,[CAQty])+IIf(Isnull([CAAdjust]),0,[CAAdjust])) As CA From
                    ((((Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo From [DeptFinish] Where ([Dept_ID]='06' or [To]='CA') " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))
                    UNION Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo From [Adjust] Where [Dept_ID]='06' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TUNIONCA
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As OCA From [DeptFinish] Where [To]='CA' and Not([To] Is Null) " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TOCA ON [TUNIONCA].[JobNo]=[TOCA].[JobNo])
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As CAQty From [DeptFinish] Where [Dept_ID]='06' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TCAQty ON [TUNIONCA].[JobNo]=[TCAQty].[JobNo])
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As CAAdjust From [Adjust] Where [Dept_ID]='06' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TCAAdjustQty ON [TUNIONCA].[JobNo]=[TCAAdjustQty].[JobNo])
                    Where IIf(Isnull([OCA]),0,[OCA])-IIf(Isnull([CAQty]),0,[CAQty])+IIf(Isnull([CAAdjust]),0,[CAAdjust]) <>0) As TCA ON [TDetail].[JobNo]= [TCA].[JobNo])

        LEFT JOIN (Select [JobNo], Max([StockGC]) As GC From
                    (Select [TGCUnion].[JobNo], [TGCUnion].[GoldKT], (IIf(Isnull([CAQty]),0,[CAQty])-IIf(Isnull([GCQty]),0,[GCQty])-IIf(Isnull([MeltQty]),0,[MeltQty])) As StockGC From
                    ((((Select [JobNo], [GoldKT] From [GCStock] Where ([GCStock].[Dept_ID]='06' or [GCStock].[Dept_ID]='12') " & con1$ & con2$ & "Group By [GCStock].[JobNo], [GCStock].[GoldKT] UNION Select [JobNo], [GoldKT] From [Melt] Where [JobNo] Like '%' " & con1$ & con2$ & con3$ & "Group By [Melt].[JobNo], [Melt].[GoldKT]) As TGCUnion
                    LEFT JOIN (Select [JobNo], [GoldKT], Sum([Qty]) As CAQty From [GCStock] Where [GCStock].[Dept_ID]='06' " & con1$ & con2$ & "Group By [GCStock].[JobNo], [GCStock].[GoldKT]) As TCAStock ON [TGCUnion].[JobNo]=[TCAStock].[JobNo] and [TGCUnion].[GoldKT]=[TCAStock].[GoldKT])
                    LEFT JOIN (Select [JobNo], [GoldKT], Sum([Qty]) As GCQty From [GCStock] Where [GCStock].[Dept_ID]='12' " & con1$ & con2$ & "Group By [GCStock].[JobNo], [GCStock].[GoldKT]) As TGCStock ON [TGCUnion].[JobNo]=[TGCStock].[JobNo] and [TGCUnion].[GoldKT]=[TGCStock].[GoldKT])
                    LEFT JOIN (Select [JobNo], [GoldKT], Sum([Qty]) As MeltQty From [Melt] Where [JobNo] Like '%' " & con1$ & con2$ & con3$ & "Group By [Melt].[JobNo], [Melt].[GoldKT]) As TMelt ON [TGCUnion].[JobNo]=[TMelt].[JobNo] and [TGCUnion].[GoldKT]=[TMelt].[GoldKT]))
                    Group By [JobNo] Having Max([StockGC])<>0) As TGC ON [TDetail].[JobNo]= [TGC].[JobNo]) "
       
        LEFT JOIN (Select [TUNIONGD].[JobNo], (IIf(Isnull([OGD]),0,[OGD])-IIf(Isnull([GDQty]),0,[GDQty])+IIf(Isnull([GDAdjust]),0,[GDAdjust])) As GD From
                    ((((Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo From [DeptFinish] Where ([Dept_ID]='07' or [To]='GD') " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))
                    UNION Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo From [Adjust] Where [Dept_ID]='07' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TUNIONGD
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As OGD From [DeptFinish] Where [To]='GD' and Not([To] Is Null) " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TOGD ON [TUNIONGD].[JobNo]=[TOGD].[JobNo])
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As GDQty From [DeptFinish] Where [Dept_ID]='07' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TGDQty ON [TUNIONGD].[JobNo]=[TGDQty].[JobNo])
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As GDAdjust From [Adjust] Where [Dept_ID]='07' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TGDAdjustQty ON [TUNIONGD].[JobNo]=[TGDAdjustQty].[JobNo])
                    Where IIf(Isnull([OGD]),0,[OGD])-IIf(Isnull([GDQty]),0,[GDQty])+IIf(Isnull([GDAdjust]),0,[GDAdjust]) <>0) As TGD ON [TDetail].[JobNo]= [TGD].[JobNo])
      
        LEFT JOIN (Select [TUNIONST].[JobNo], (IIf(Isnull([OST]),0,[OST])-IIf(Isnull([STQty]),0,[STQty])+IIf(Isnull([STAdjust]),0,[STAdjust])) As ST From
                    ((((Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo From [DeptFinish] Where ([Dept_ID]='08' or [To]='ST') " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))
                    UNION Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo From [Adjust] Where [Dept_ID]='08' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TUNIONST
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As OST From [DeptFinish] Where [To]='ST' and Not([To] Is Null) " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TOST ON [TUNIONST].[JobNo]=[TOST].[JobNo])
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As STQty From [DeptFinish] Where [Dept_ID]='08' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TSTQty ON [TUNIONST].[JobNo]=[TSTQty].[JobNo])
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As STAdjust From [Adjust] Where [Dept_ID]='08' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TSTAdjustQty ON [TUNIONST].[JobNo]=[TSTAdjustQty].[JobNo])
                    Where IIf(Isnull([OST]),0,[OST])-IIf(Isnull([STQty]),0,[STQty])+IIf(Isnull([STAdjust]),0,[STAdjust]) <>0) As TST ON [TDetail].[JobNo]= [TST].[JobNo])
      
        LEFT JOIN (Select [TUNIONPL].[JobNo], (IIf(Isnull([OPL]),0,[OPL])-IIf(Isnull([PLQty]),0,[PLQty])+IIf(Isnull([PLAdjust]),0,[PLAdjust])) As PL From
                    ((((Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo From [DeptFinish] Where ([Dept_ID]='10' or [To]='PL') " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))
                    UNION Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo From [Adjust] Where [Dept_ID]='10' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TUNIONPL
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As OPL From [DeptFinish] Where [To]='PL' and Not([To] Is Null) " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TOPL ON [TUNIONPL].[JobNo]=[TOPL].[JobNo])
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As PLQty From [DeptFinish] Where [Dept_ID]='10' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TPLQty ON [TUNIONPL].[JobNo]=[TPLQty].[JobNo])
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As PLAdjust From [Adjust] Where [Dept_ID]='10' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TPLAdjustQty ON [TUNIONPL].[JobNo]=[TPLAdjustQty].[JobNo])
                    Where IIf(Isnull([OPL]),0,[OPL])-IIf(Isnull([PLQty]),0,[PLQty])+IIf(Isnull([PLAdjust]),0,[PLAdjust]) <>0) As TPL ON [TDetail].[JobNo]= [TPL].[JobNo])
     
        LEFT JOIN (Select [TUNIONRH].[JobNo], (IIf(Isnull([ORH]),0,[ORH])-IIf(Isnull([RHQty]),0,[RHQty])+IIf(Isnull([RHAdjust]),0,[RHAdjust])) As RH From
                    ((((Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo From [DeptFinish] Where ([Dept_ID]='11' or [To]='RH') " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))
                    UNION Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo From [Adjust] Where [Dept_ID]='10' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TUNIONRH
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As ORH From [DeptFinish] Where [To]='RH' and Not([To] Is Null) " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TORH ON [TUNIONRH].[JobNo]=[TORH].[JobNo])
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As RHQty From [DeptFinish] Where [Dept_ID]='11' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TRHQty ON [TUNIONRH].[JobNo]=[TRHQty].[JobNo])
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As RHAdjust From [Adjust] Where [Dept_ID]='11' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TRHAdjustQty ON [TUNIONRH].[JobNo]=[TRHAdjustQty].[JobNo])
                    Where IIf(Isnull([ORH]),0,[ORH])-IIf(Isnull([RHQty]),0,[RHQty])+IIf(Isnull([RHAdjust]),0,[RHAdjust]) <>0) As TRH ON [TDetail].[JobNo]= [TRH].[JobNo])
      
        LEFT JOIN (Select [TPKUnion].[JobNo], (IIf(Isnull([PKQty]),0,[PKQty])-IIf(Isnull([PKRetQty]),0,[PKRetQty])) As PK From
                    (((Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo From [DeptFinish] Where [Dept_ID]='13' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))
                    UNION Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo From [PackingRet] Where [LotNo] Like '%' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TPKUnion
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As PKQty From [DeptFinish] Where [Dept_ID]='13' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TPKQty ON [TPKUnion].[JobNo]=[TPKQty].[JobNo])
                    LEFT JOIN (Select IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10)) As JobNo, Sum([Qty]) As PKRetQty From [PackingRet] Where [LotNo] Like '%' " & IIf(con1$ <> "", "and [LotNo] Like '" & txtJobNo.Text & "%' ", "") & con2$ & con3$ & "Group By IIf(Instr([LotNo],'/')<>0,Mid([LotNo],1,12),Mid([LotNo],1,10))) As TPKRet ON [TPKUnion].[JobNo]=[TPKRet].[JobNo])) As TPK ON [TDetail].[JobNo]= [TPK].[JobNo])
       
        LEFT JOIN (Select [StoneControl].[Job No] As JobNo, [Dia], [Stn] From (([StoneControl]
                    LEFT JOIN (Select [Job No], Max(IIf(Isnull([SendQty]),0,[SendQty])) As Dia From
                    (Select [StoneControl].[ID], [StoneControl].[Job No], ([StoneControl].[Qty(Order)]-IIf(Isnull([TNJDiaSendDate].[SQty]),0,[TNJDiaSendDate].[SQty])) As SendQty From [StoneControl]
                    LEFT JOIN (Select [Send Date].[ID], Sum([Send Date].[Qty]) As SQty From [Send Date] Group By [Send Date].[ID]) As TNJDiaSendDate ON [StoneControl].[ID]=[TNJDiaSendDate].[ID]
                    Where [Description] Like '%DIA%' and [Order Date]>=#02/21/2004# " & IIf(con1$ <> "", "and [Job No] Like '" & txtJobNo.Text & "' ", "") & IIf(con2$ <> "", "and [ST] Like '" & txtStyleNo.Text & "' ", "") & IIf(con3$ <> "", "and [CusStyle] Like '" & txtCusStyleNo.Text & "' ", "") & IIf(con4$ <> "", "and [PO No] Like '" & txtPONO.Text & "' ", "") & ") Group By [Job No]) As TNJDia ON [StoneControl].[Job No]=[TNJDia].[Job No])
                    LEFT JOIN (Select [Job No], Max(IIf(Isnull([SendQty]),0,[SendQty])) As Stn From
                    (Select [StoneControl].[ID], [StoneControl].[Job No], ([StoneControl].[Qty(Order)]-IIf(Isnull([TNJStnSendDate].[SQty]),0,[TNJStnSendDate].[SQty])) As SendQty From [StoneControl]
                    LEFT JOIN (Select [Send Date].[ID], Sum([Send Date].[Qty]) As SQty From [Send Date] Group By [Send Date].[ID]) As TNJStnSendDate ON [StoneControl].[ID]=[TNJStnSendDate].[ID]
                    Where Not([Description] Like '%DIA%') and [Order Date]>=#02/21/2004# " & IIf(con1$ <> "", "and [Job No] Like '" & txtJobNo.Text & "' ", "") & IIf(con2$ <> "", "and [ST] Like '" & txtStyleNo.Text & "' ", "") & IIf(con3$ <> "", "and [CusStyle] Like '" & txtCusStyleNo.Text & "' ", "") & IIf(con4$ <> "", "and [PO No] Like '" & txtPONO.Text & "' ", "") & ") Group By [Job No]) As TNJStn ON [StoneControl].[Job No]=[TNJStn].[Job No])
                    Where [Order Date] >= #02/21/2004# " & IIf(con1$ <> "", "and [StoneControl].[Job No] Like '" & txtJobNo.Text & "' ", "") & IIf(con2$ <> "", "and [ST] Like '" & txtStyleNo.Text & "' ", "") & IIf(con3$ <> "", "and [CusStyle] Like '" & txtCusStyleNo.Text & "' ", "") & IIf(con4$ <> "", "and [PO No] Like '" & txtPONO.Text & "' ", "")
                    Union
                    Select [NSStoneControl].[Job No] As JobNo, [Dia], [Stn] From (([NSStoneControl]
                    LEFT JOIN (Select [Job No], Max(IIf(Isnull([SendQty]),0,[SendQty])) As Dia From
                    (Select [NSStoneControl].[ID], [NSStoneControl].[Job No], ([NSStoneControl].[Qty(Order)]-IIf(Isnull([TNSDiaSendDate].[SQty]),0,[TNSDiaSendDate].[SQty])) As SendQty From [NSStoneControl]
                    LEFT JOIN (Select [NSSend Date].[ID], Sum([NSSend Date].[Qty]) As SQty From [NSSend Date] Group By [NSSend Date].[ID]) As TNSDiaSendDate ON [NSStoneControl].[ID]=[TNSDiaSendDate].[ID]
                    Where [Description] Like '%DIA%' and [Order Date]>=#02/21/2004# " & IIf(con1$ <> "", "and [Job No] Like '" & txtJobNo.Text & "' ", "") & IIf(con2$ <> "", "and [ST] Like '" & txtStyleNo.Text & "' ", "") & IIf(con3$ <> "", "and [CusStyle] Like '" & txtCusStyleNo.Text & "' ", "") & IIf(con4$ <> "", "and [PO No] Like '" & txtPONO.Text & "' ", "") & ") Group By [Job No]) As TNSDia ON [NSStoneControl].[Job No]=[TNSDia].[Job No])
                    LEFT JOIN (Select [Job No], Max(IIf(Isnull([SendQty]),0,[SendQty])) As Stn From
                    (Select [NSStoneControl].[ID], [NSStoneControl].[Job No], ([NSStoneControl].[Qty(Order)]-IIf(Isnull([TNSStnSendDate].[SQty]),0,[TNSStnSendDate].[SQty])) As SendQty From [NSStoneControl]
                    LEFT JOIN (Select [NSSend Date].[ID], Sum([NSSend Date].[Qty]) As SQty From [NSSend Date] Group By [NSSend Date].[ID]) As TNSStnSendDate ON [NSStoneControl].[ID]=[TNSStnSendDate].[ID]
                    Where Not([Description] Like '%DIA%') and [Order Date]>=#02/21/2004# " & IIf(con1$ <> "", "and [Job No] Like '" & txtJobNo.Text & "' ", "") & IIf(con2$ <> "", "and [ST] Like '" & txtStyleNo.Text & "' ", "") & IIf(con3$ <> "", "and [CusStyle] Like '" & txtCusStyleNo.Text & "' ", "") & IIf(con4$ <> "", "and [PO No] Like '" & txtPONO.Text & "' ", "") & ") Group By [Job No]) As TNSStn ON [NSStoneControl].[Job No]=[TNSStn].[Job No])
                    Where [Order Date] >= #02/21/2004# " & IIf(con1$ <> "", "and [NSStoneControl].[Job No] Like '" & txtJobNo.Text & "' ", "") & IIf(con2$ <> "", "and [ST] Like '" & txtStyleNo.Text & "' ", "") & IIf(con3$ <> "", "and [CusStyle] Like '" & txtCusStyleNo.Text & "' ", "") & IIf(con4$ <> "", "and [PO No] Like '" & txtPONO.Text & "' ", "") & ") As TStoneControl ON [TDetail].[JobNo]= [TStoneControl].[JobNo])
     
        LEFT JOIN (Select [JobNo], Min([Qty]) As Component From " _
                    (Select [JobNo], [Description], Sum([Send]) As Qty From [Component] Where [JobNo] Like '%' " & con1$ & con2$ & con3$ & "Group By [JobNo], [Description])
                    Group By [JobNo]) As TComponent ON [TDetail].[JobNo]= [TComponent].[JobNo])
       
        LEFT JOIN (Select [JobNo], [Remark] From [ReqPlan] Where [JobNo] Like '%' " & con1$ & con2$ & ") As TRemark ON [TDetail].[JobNo]= [TRemark].[JobNo])
                    Where [TDetail].[JobNo] Like '%' " & con8$ & con10$ & con12$ & "Order By [TDetail].[JobNo]

    <<<<<<<<<<<<<<<<<<<<< END <<<<<<<<<<<<<<<<<<<<<<<<<<



  • Ouch!!

    This is definitely a front page worthy WTF.
     

    I stopped reading after the third line, this makes perl look pretty*. 

     

    *) I don't hate/love perl 



  • Holy sweet mary, mother of bob!! There are probably more joins in there than I have ever used in my entire life, and a few unions thrown in for good measure too! I'll believe that this one is real purely on the basis that nobody would go that far for a prank.

     

    SQL text that is big enough to be published in a hardback edition is a bad bad thing. 



  • OH HOLY CRAP

     

     

    HOLY CRAP

     

     

     

     

    HOLY CRAP 

     

    I was like, oh this is one of those fucked-up long queries. Nothing new, eh. I pressed pagedown...

    And pressed pagedown...

    And pressed pagedown...

    And pressed pagedown...

    And pressed pagedown...

    And...

    And couldn't help but laugh the crazed laugh of the desperate...

    And pressed pagedown...

    Even when I recuded the font-size three times, it doesn't fit on screen.
    I run 1600x1200.



  • — wow, according to the logs this page generates about 400 queries .. oh wait, that's only one 400-lines query !



  • Oh my sweet Lord... please tell me that was generated somehow, and no one actually typed that in by hand...



  • @cconroy said:

    Oh my sweet Lord... please tell me that was generated somehow, and no one actually typed that in by hand...

    The OP mentioned that it was an Access query. It was generated in the background when the QueryBuilder was used.



  • This one really deserves a "goggles" response. zedhex, I hope you're getting paid well.



  • er...no it wasn't. This extraordinary example of incompetence was created over a 4 year period by accreting more and more Access code and adding it onto the end using left joins. If you disentangle this mess you can see hoe it was done. At the end of this job I told the boss of the company - "This is what you get when you hire people to design a database without ever having read a book on database design".



  • that is definately the worst WTF ive ever seen



  • @zedhex said:

    er...no it wasn't. This extraordinary example of incompetence was created over a 4 year period by accreting more and more Access code and adding it onto the end using left joins. If you disentangle this mess you can see hoe it was done. At the end of this job I told the boss of the company - "This is what you get when you hire people to design a database without ever having read a book on database design".

    This query probably wouldn't even display in the query builder with all the derived tables, unions, etc.  My gut tells me the individual sections of the query were designed in the query builder and then copy/pasted into this monstrosity.

    I've seen similar stuff in systems accessing MsSql databases, although not quite as bad.  I've seen several web based reports based on a series of 7 unions with each select including several sub selects.  The SQL took up 3 pages of text on a 1600 x 1200 screen, so it was comparable to this.  Those queries were definitely speed demons as well.

    This is also one of the reasons I prefer to have a DBA in charge of the database and all changes must go through him.  Any competent DBA would reject this outright.  They might be pricks but their job is to ensure the database runs efficiently and this definitely runs counter to that.

     



  • @zedhex said:

    er...no it wasn't. This extraordinary example of incompetence was created over a 4 year period by accreting more and more Access code and adding it onto the end using left joins. If you disentangle this mess you can see hoe it was done. At the end of this job I told the boss of the company - "This is what you get when you hire people to design a database without ever having read a book on database design".

     My brain feels really sore after seeing that...it should be sealed away and never again exposed to the light of day. I think its true purpose is to drain IQ points...arghhhh 



  • So how much were you able to simplify it?



  • @rmr said:

    So how much were you able to simplify it?

    SELECT * FROM Orders 



  • It eventually turned into a full-on ground-up database re-write. I basically told them that even if I managed to patch it up, the real problem lay in their database design (if you think this is bad, you should have seen their database 'Schema' - 300+ tables and not one foreign key!!!). I did manage to double the speed of this thing - but believe me, I really don't want to go into it.

    zedhex 



  • faints



  • I think I know what this is for. I think I even know who you're contracting for, so: my apologies. Also, my sympathies.



  • Tsu Tzu..., hmmmm,  now I am really curious - I kept this one under my hat for a long time to protect the guilty, and I'm not going to let the cat out of the bag either. Given that I am one of very few people who have ever seen this - have you ever worked in South East Asia?



  • Guys, this is not *so big sql, I used to output 3-4 pages of sql with my own builder (queries were built up from small building blocks in branches joining downward to a final point: the final query). I rarely look into the generated text. Debugging happens at query builder level.



  • That is so horrible, I almost clicked on the "report abuse" link.



  • eh???? tbtvn, you have got to be kidding - I guess if you wanted to fake something, it could be done. But this is actual production code. In my 20+ years in this game I have never seen anything this bad. Soooo.... show me what you've got - if it is any worse than this I'll be very surprised.


Log in to reply