Enjoy this sql statement



  • <FONT color=#0000f0 size=2>

    SELECT</FONT><FONT size=2> </FONT><FONT color=#0000f0 size=2>DISTINCT</FONT><FONT size=2>

    T1</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>intReport </FONT><FONT color=#0000f0 size=2>AS</FONT><FONT size=2> Report</FONT><FONT color=#0000f0 size=2>#,

    </FONT><FONT size=2>

    T1</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>intRequestPK </FONT><FONT color=#0000f0 size=2>AS</FONT><FONT size=2> Request</FONT><FONT color=#0000f0 size=2>#,</FONT><FONT size=2>

    T1</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>vcharReportTitle </FONT><FONT color=#0000f0 size=2>AS</FONT><FONT size=2> </FONT><FONT color=#0000f0 size=2>[</FONT><FONT size=2>Description </FONT><FONT color=#0000f0 size=2>of</FONT><FONT size=2> Report</FONT><FONT color=#0000f0 size=2>],</FONT><FONT size=2>

    T1</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>nvcharDescReq </FONT><FONT color=#0000f0 size=2>AS</FONT><FONT size=2> </FONT><FONT color=#0000f0 size=2>[</FONT><FONT size=2>Detail </FONT><FONT color=#0000f0 size=2>of</FONT><FONT size=2> Request</FONT><FONT color=#0000f0 size=2>],</FONT><FONT size=2>

    T1</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>intPriority </FONT><FONT color=#0000f0 size=2>AS</FONT><FONT size=2> Priority</FONT><FONT color=#0000f0 size=2>,</FONT><FONT size=2>

    tblPerson_2</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>vcharFullName </FONT><FONT color=#0000f0 size=2>AS</FONT><FONT size=2> Requestor</FONT><FONT color=#0000f0 size=2>,</FONT><FONT size=2>

    tblPerson_1</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>vcharFullName </FONT><FONT color=#0000f0 size=2>AS</FONT><FONT size=2> </FONT><FONT color=#0000f0 size=2>[</FONT><FONT size=2>Business Contact</FONT><FONT color=#0000f0 size=2>],</FONT><FONT size=2>

    T2</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>vcharFullName </FONT><FONT color=#0000f0 size=2>AS</FONT><FONT size=2> Analyst</FONT><FONT color=#0000f0 size=2>,</FONT><FONT size=2>

    T5</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>vcharFullName </FONT><FONT color=#0000f0 size=2>AS</FONT><FONT size=2> Programmer</FONT><FONT color=#0000f0 size=2>,</FONT><FONT size=2>

    T7</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>charSubStatus </FONT><FONT color=#0000f0 size=2>AS</FONT><FONT size=2> Status</FONT><FONT color=#0000f0 size=2>,</FONT><FONT size=2>

    T1</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>datReqDate </FONT><FONT color=#0000f0 size=2>AS</FONT><FONT size=2> </FONT><FONT color=#0000f0 size=2>[</FONT><FONT color=#ff0000 size=2>Date</FONT><FONT size=2> Requested</FONT><FONT color=#0000f0 size=2>],</FONT><FONT size=2>

    T1</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>datAnalystReceived </FONT><FONT color=#0000f0 size=2>AS</FONT><FONT size=2> </FONT><FONT color=#0000f0 size=2>[</FONT><FONT color=#ff0000 size=2>Date</FONT><FONT size=2> </FONT><FONT color=#0000f0 size=2>to</FONT><FONT size=2> Analyst</FONT><FONT color=#0000f0 size=2>],</FONT><FONT size=2>

    T1</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>datProgReceived </FONT><FONT color=#0000f0 size=2>AS</FONT><FONT size=2> </FONT><FONT color=#0000f0 size=2>[</FONT><FONT color=#ff0000 size=2>Date</FONT><FONT size=2> </FONT><FONT color=#0000f0 size=2>to</FONT><FONT size=2> Programming</FONT><FONT color=#0000f0 size=2>],</FONT><FONT size=2>

    T1</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>datQACompletion </FONT><FONT color=#0000f0 size=2>AS</FONT><FONT size=2> </FONT><FONT color=#0000f0 size=2>[</FONT><FONT color=#ff0000 size=2>Date</FONT><FONT size=2> </FONT><FONT color=#0000f0 size=2>to</FONT><FONT size=2> QA</FONT><FONT color=#0000f0 size=2>],</FONT><FONT size=2>

    T1</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>datSignOff </FONT><FONT color=#0000f0 size=2>AS</FONT><FONT size=2> </FONT><FONT color=#0000f0 size=2>[</FONT><FONT color=#ff0000 size=2>Date</FONT><FONT size=2> </FONT><FONT color=#0000f0 size=2>to</FONT><FONT size=2> </FONT><FONT color=#0000f0 size=2>User</FONT><FONT size=2> Signoff</FONT><FONT color=#0000f0 size=2>],</FONT><FONT size=2>

    T1</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>datTurnOver </FONT><FONT color=#0000f0 size=2>AS</FONT><FONT size=2> </FONT><FONT color=#0000f0 size=2>[</FONT><FONT color=#ff0000 size=2>Date</FONT><FONT size=2> </FONT><FONT color=#0000f0 size=2>to</FONT><FONT size=2> Turnover</FONT><FONT color=#0000f0 size=2>]</FONT><FONT size=2>

     

    </FONT><FONT color=#0000f0 size=2>

    FROM</FONT><FONT size=2> tblReportRequest T1

    INNER JOIN tblJunctionProgrammer T3 </FONT><FONT color=#0000f0 size=2>ON</FONT><FONT size=2> ISNULL</FONT><FONT color=#0000f0 size=2>(</FONT><FONT size=2>T1</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>intProgrammerFK</FONT><FONT color=#0000f0 size=2>,</FONT><FONT size=2>

    </FONT><FONT color=#0000f0 size=2>(SELECT</FONT><FONT size=2> intProgrammerPK

    </FONT><FONT color=#0000f0 size=2>FROM</FONT><FONT size=2> tblJunctionProgrammer

    </FONT><FONT color=#0000f0 size=2>WHERE</FONT><FONT size=2> intPersonFK </FONT><FONT color=#0000f0 size=2>=</FONT><FONT size=2>

    </FONT><FONT color=#0000f0 size=2>(SELECT</FONT><FONT size=2> intPersonPK

    </FONT><FONT color=#0000f0 size=2>FROM</FONT><FONT size=2> tblPerson

    </FONT><FONT color=#0000f0 size=2>WHERE</FONT><FONT size=2> vcharFullName </FONT><FONT color=#0000f0 size=2>LIKE</FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2>'Not %'</FONT><FONT color=#0000f0 size=2>)))</FONT><FONT size=2> </FONT><FONT color=#0000f0 size=2>=</FONT><FONT size=2> T3</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>intProgrammerPK

    INNER JOIN tblJunctionAnalyst T4 </FONT><FONT color=#0000f0 size=2>ON</FONT><FONT size=2> ISNULL</FONT><FONT color=#0000f0 size=2>(</FONT><FONT size=2>T1</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>intAnalystFK</FONT><FONT color=#0000f0 size=2>,</FONT><FONT size=2>

    </FONT><FONT color=#0000f0 size=2>(SELECT</FONT><FONT size=2> intAnalystPK

    </FONT><FONT color=#0000f0 size=2>FROM</FONT><FONT size=2> tblJunctionAnalyst

    </FONT><FONT color=#0000f0 size=2>WHERE</FONT><FONT size=2> intPersonFK </FONT><FONT color=#0000f0 size=2>=</FONT><FONT size=2>

    </FONT><FONT color=#0000f0 size=2>(SELECT</FONT><FONT size=2> intPersonPK

    </FONT><FONT color=#0000f0 size=2>FROM</FONT><FONT size=2> tblPerson

    </FONT><FONT color=#0000f0 size=2>WHERE</FONT><FONT size=2> vcharFullName </FONT><FONT color=#0000f0 size=2>LIKE</FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2>'Not %'</FONT><FONT color=#0000f0 size=2>)))</FONT><FONT size=2> </FONT><FONT color=#0000f0 size=2>=</FONT><FONT size=2> T4</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>intAnalystPk

    INNER JOIN tblPerson T2 </FONT><FONT color=#0000f0 size=2>ON</FONT><FONT size=2> T4</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>intPersonFK </FONT><FONT color=#0000f0 size=2>=</FONT><FONT size=2> T2</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>intPersonPK

    INNER JOIN tblPerson T5 </FONT><FONT color=#0000f0 size=2>ON</FONT><FONT size=2> T3</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>intPersonFK </FONT><FONT color=#0000f0 size=2>=</FONT><FONT size=2> T5</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>intPersonPK

    INNER JOIN tlkpStatus T7 </FONT><FONT color=#0000f0 size=2>ON</FONT><FONT size=2> T1</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>intStatusFK </FONT><FONT color=#0000f0 size=2>=</FONT><FONT size=2> T7</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>intStatusPk

    INNER JOIN tblPerson tblPerson_1 </FONT><FONT color=#0000f0 size=2>ON</FONT><FONT size=2> T1</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>intPersonFKContact </FONT><FONT color=#0000f0 size=2>=</FONT><FONT size=2> tblPerson_1</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>intPersonPK

    INNER JOIN tblPerson tblPerson_2 </FONT><FONT color=#0000f0 size=2>ON</FONT><FONT size=2> T1</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>intPersonFKRequestor </FONT><FONT color=#0000f0 size=2>=</FONT><FONT size=2> tblPerson_2</FONT><FONT color=#0000f0 size=2>.</FONT><FONT size=2>intPersonPK

    CROSS JOIN tblPerson T6

    </FONT>


  • So many selects and joins makes my head hurt. I assume that with the aliased fields that is is being databound to a report.



  • The program itself gets even worse.  I tried to convince my boss their was a better design for the program.  The app basically generates an excel xml file based on the data.  The scary part is that my was very adamant that most of it be hardcoded as a string in the app. The point of the app was to make a coworkers life easier. basically every week she had to generate a report in excel by copying and pasting data off of our intranet site.

     if yout think that is bad.  you should see the hodgepodge my company uses.

     

    *my job is to help eliminate some of the chaos*



  • AAAAAAAAARGH! My head just blew up.



  • OK, it is doing a few things, but it isn't the worst I have seen. 16(right?) selects; no problem; a few nested queries... fun, but doable. The worst I can say is that the aliases are terrible (unless this is runtime auto-gen code, in which case who cares?), and that I personally would never use CROSS JOIN without explaining why. Nothing wrong with CROSS JOIN, as long as people understand what they are doing ;-p



  • Arg, yet another post that crashes IE7 on all my machines :(



  • Yeah, this crashes IE 6 as well.  Had to view in Firefox.  Fucking ridiculous.



  • @Hitsuji said:

    Arg, yet another post that crashes IE7 on all my machines :(

    @luke727 said:


    Yeah, this crashes IE 6 as well.  Had to view in Firefox.  Fucking ridiculous.

    There is a simple solution for this problem: don't use IE.
     

     


Log in to reply