• (unregistered)

    I guess that's why the periods don't line up on the right side!

  • phpkid (cs)

    <font face="Verdana" size="2">What kind of table name ' S_C_S_L' is?</font><font face="Verdana">

    <font size="2">JD</font></font>

  • (unregistered)

    I can't use this sproc, which returns data which is needed fairly routinely, for anything else other than this one report!

    All you have to do is use replace(text, ".", "")

  • Sweets (cs)

    The real WTF is using Access for reporting.. 

    Crystal Reports for life!

  • loneprogrammer (cs) in reply to
    All you have to do is use replace(text, ".", "")

    Yes, but you are totally missing the point.  Why should this guy's code have to clean up the mess made by the first guy's code?

    One good WTF does not deserve another.

  • Bustaz Kool (cs) in reply to phpkid

    phpkid:
    <FONT face=Verdana size=2>What kind of table name ' S_C_S_L' is?</FONT><FONT face=Verdana>

    </FONT>

    State, City, Street and Level (Floor).... Wasn't that obvious?   I also enjoyed the column naming convention.  Does this guy get paid per underscore/character ratio?

    Column Name: R (A clear reference to the Reference)

    Column Name: B_D (Because BD is just too confusing)

    You have my sympathies...

  • UncleMidriff (cs)

    One small correction:  In my original e-mail to Alex, the line, "The stored procedure that feeds this report figures all the totals via 80 different select queries and temp tables" was actually, "The sproc that
    feeds this report figures all the totals via <FONT color=#0000ff>about 80 bajillion</FONT>
    different select queries and temp tables."  I just wanted to make sure that everyone knew that the number, whether it be 80 or 80 bajillion, is an exaggeration. 

  • b0b0b0b (cs)

    might be an interesting exercise to come up with a functional test suite and rewrite some of the internals

  • UncleMidriff (cs) in reply to Bustaz Kool
    Bustaz Kool:

    [image] phpkid wrote:
    <FONT face=Verdana size=2>What kind of table name ' S_C_S_L' is?</FONT><FONT face=Verdana>

    </FONT>

    State, City, Street and Level (Floor).... Wasn't that obvious?   I also enjoyed the column naming convention.  Does this guy get paid per underscore/character ratio?

    Column Name: R (A clear reference to the Reference)

    Column Name: B_D (Because BD is just too confusing)

    You have my sympathies...

    S_C_S_L = Specific Course Seminar Listing.  Yeah, I know that "Course" and "Seminar" mean virtually the same thing (in the context of this system anyway).  But in this application the words are used interchangeably based on whatever the author happened to be smoking at the time or, as this example shows, simultaneously.  You know, because for reasons and such.

     

  • (unregistered)

    QUOTE
    Which is good, because now that report won't have to work so hard formatting the data on its own.

    Aside from distributing the load away from the report, which already has alot of formatting to do, this also enforces a standard look and feel in your reports, by making sure the same number of periods are in the ............


    QUOTE

    This is also good because now I can't use this sproc, which returns data which is needed fairly routinely, for anything else other than this one report! That'll teach me to be lazy, trying to reuse someone else's code.

    I agree with you.  One of the down sides to code re-use is the fact that if you change it in one place it will break in other places.  By implementing report-specific formatting in the stored procedure, you can make sure nobody else calls your stored procedure, and will mean you dont have to worry about breaking other peoples code when you change your stored procedure.

    As you can see, sometimes what seems like a WTF at first glance has some sound fundamental reasons for why it was done the way it was done.

  • (unregistered)

    <FONT face="Courier New">CASE WHEN DATEPART(mm,S_C_S_L.B_D) > 9
    AND DATEPART(mm,S_C_S_L.B_D) < 13 THEN 1
        WHEN DATEPART(mm,S_C_S_L.B_D) > 0
    AND DATEPART(mm,S_C_S_L.B_D) < 4 THEN 2
        WHEN DATEPART(mm,S_C_S_L.B_D) > 3
    AND DATEPART(mm,S_C_S_L.B_D) < 7 THEN 3
        WHEN DATEPART(mm,S_C_S_L.B_D) > 6
    AND DATEPART(mm,S_C_S_L.B_D) < 10 THEN 4
        <FONT color=#ff0000><FONT size=6>ELSE 0</FONT>
    </FONT>END AS QUARTER</FONT>

    A mini-WTF within the code. Since when would it return a Month of 0? If the date is NULL then it will return NULL, not 0. Um. Hello????

     

  • loneprogrammer (cs) in reply to
    One of the down sides to code re-use is the fact that if you change it in one place it will break in other places.

    That is not because of code re-use.  That always happens.  You are implying that we should never call the same function more than once.  That is dumb.

    At least with code re-use, when you fix something in one place, it gets fixed everywhere.  With rape-and-paste, you may have to fix something many times!

  • Jon Limjap (cs) in reply to
    :

    QUOTE
    Which is good, because now that report won't have to work so hard formatting the data on its own.

    Aside from distributing the load away from the report, which already has alot of formatting to do, this also enforces a standard look and feel in your reports, by making sure the same number of periods are in the ............


    QUOTE

    This is also good because now I can't use this sproc, which returns data which is needed fairly routinely, for anything else other than this one report! That'll teach me to be lazy, trying to reuse someone else's code.

    I agree with you.  One of the down sides to code re-use is the fact that if you change it in one place it will break in other places.  By implementing report-specific formatting in the stored procedure, you can make sure nobody else calls your stored procedure, and will mean you dont have to worry about breaking other peoples code when you change your stored procedure.

    As you can see, sometimes what seems like a WTF at first glance has some sound fundamental reasons for why it was done the way it was done.



    ^^WTF!

    Yup sweetheart, it also assures that once somebody messes up with your stored procs because they hate you, nobody else will get hurt. So much for camaraderie.[:P]

    So much for "sound" and "fundamental."
  • (unregistered) in reply to
    :

    QUOTE
    Which is good, because now that report won't have to work so hard formatting the data on its own.

    Aside from distributing the load away from the report, which already has alot of formatting to do, this also enforces a standard look and feel in your reports, by making sure the same number of periods are in the ............


    QUOTE

    This is also good because now I can't use this sproc, which returns data which is needed fairly routinely, for anything else other than this one report! That'll teach me to be lazy, trying to reuse someone else's code.

    I agree with you.  One of the down sides to code re-use is the fact that if you change it in one place it will break in other places.  By implementing report-specific formatting in the stored procedure, you can make sure nobody else calls your stored procedure, and will mean you dont have to worry about breaking other peoples code when you change your stored procedure.

    As you can see, sometimes what seems like a WTF at first glance has some sound fundamental reasons for why it was done the way it was done.

    Congratulations!  The "WTF Comment" of the day award! [<:o)]

    Formatting at the on the REPORT?  Who the hell would ever do THAT? 

  • foxyshadis (cs)

    Obviously the proper solution is to generate the entire report (in RTF) in the SP, and then have access display it by opening an instance of word. Of course you want to inline all that RTF conversion code for performance reasons, and have split it up into chunks so access can call several in parallel, holding the connections open while using it.

    Oh, and for performance reasons make sure to read/write lock the database beforehand. Just because a bored manager is skimming reports midday is no reason why everyone else in the company should be slowing him down by doing real work.

  • (unregistered) in reply to

    DATEPART(q, <FONT face="Courier New">S_C_S_L.B_D) would be to easy I think[;)]</FONT>

  • diGriz (cs) in reply to
    :

    <FONT face="Courier New">CASE WHEN DATEPART(mm,S_C_S_L.B_D) > 9
    AND DATEPART(mm,S_C_S_L.B_D) < 13 THEN 1
        WHEN DATEPART(mm,S_C_S_L.B_D) > 0
    AND DATEPART(mm,S_C_S_L.B_D) < 4 THEN 2
        WHEN DATEPART(mm,S_C_S_L.B_D) > 3
    AND DATEPART(mm,S_C_S_L.B_D) < 7 THEN 3
        WHEN DATEPART(mm,S_C_S_L.B_D) > 6
    AND DATEPART(mm,S_C_S_L.B_D) < 10 THEN 4
        <FONT color=#ff0000><FONT size=6>ELSE 0</FONT>
    </FONT>END AS QUARTER</FONT>

    A mini-WTF within the code. Since when would it return a Month of 0? If the date is NULL then it will return NULL, not 0. Um. Hello????

    This is the quarter. Since he checked for all months (>0 and <13), this ELSE part will never be used (maybe he did it by reflex). But I don't get the quarter numbers. The first quarter usually is from January to March, while his sproc defines it from October to December. All the other quarters are also defined 3 months earlier.

  • (unregistered) in reply to diGriz

    I don't think it is true that you would get NULL when ... err, B_D is null. You will get the value from the ELSE branch.

    At any rate, I prefer it written this way, even if B_D was NOT NULL. It's better than saying for instance "ELSE 4" and leaving the last WHEN out. (so it's either 1, 2, 3 and all other cases are 4).

    Here's some junk against IBM's DB2:

    create table foo (bar integer);

    insert into foo values (1), (0), (null);

    select case when bar > 0 then 1 when bar <= 0 then 0 else -1 end as x from foo;

    <font face="Courier New">+----+
    | x  |
    +----+
    | 1  |
    | 0  |
    | -1 |
    +----+
    </font>
    Neat.

  • (unregistered) in reply to diGriz
    diGriz:

    But I don't get the quarter numbers. The first quarter usually is from January to March, while his sproc defines it from October to December. All the other quarters are also defined 3 months earlier.

    Methinks that these quarters refer to the fiscal year and the fiscal year of that company starts october 1.

  • emurphy (cs) in reply to
    :

    <font face="Courier New">CASE WHEN DATEPART(mm,S_C_S_L.B_D) > 9
    AND DATEPART(mm,S_C_S_L.B_D) < 13 THEN 1
        WHEN DATEPART(mm,S_C_S_L.B_D) > 0
    AND DATEPART(mm,S_C_S_L.B_D) < 4 THEN 2
        WHEN DATEPART(mm,S_C_S_L.B_D) > 3
    AND DATEPART(mm,S_C_S_L.B_D) < 7 THEN 3
        WHEN DATEPART(mm,S_C_S_L.B_D) > 6
    AND DATEPART(mm,S_C_S_L.B_D) < 10 THEN 4
        <font color="#ff0000"><font size="6">ELSE 0</font>
    </font>END AS QUARTER</font>

    A mini-WTF within the code. Since when would it return a Month of 0? If the date is NULL then it will return NULL, not 0. Um. Hello????

     



    Doesn't matter; either (Month = 0) or (Month = NULL) will fail all four of the WHEN clauses, and thus fall through to the ELSE clause, returning (Quarter = 0).

  • Drak (cs) in reply to

    :
    DATEPART(q, <FONT face="Courier New">S_C_S_L.B_D) would be to easy I thinkWink</FONT>

    I'm guessing that is because their company uses their own (fiscal as mentioned above) definition of when which quarter starts.

    Drak

  • Drak (cs) in reply to Drak

    I like the way they use extremely cryptic column names in the database, but use extremely readable column names as the return columns.

    This is obviously meant to be a very very black box. You put in your input, you get the result. What happens in between is none of your business, and if you dare take a look all you will see are many many strangely sized cogs and chains and rods and levers and such.

    Drak

  • UncleMidriff (cs) in reply to Drak
    Drak:

    [image]  wrote:
    DATEPART(q, <FONT face="Courier New">S_C_S_L.B_D) would be to easy I thinkWink</FONT>

    I'm guessing that is because their company uses their own (fiscal as mentioned above) definition of when which quarter starts.

    Drak

     

    Then I would probably have used DATEPART(q, S_C_S_L.B_D)%4 + 1.

  • (unregistered)

    This is the primary reason why I don't ever use stored procedures.  I've had nothing but bad experiences with them.

    - Joshua

  • Jeff S (cs) in reply to

    By the way, note the very beginning of the SELECT statement:

    SELECT DISTINCT 

    In a select with joins and calculations, if it starts with SELECT DISTINCT, that tells you right off the bat to stop reading and re-design either the SELECT from scratch, and/or the entire database.

  • (unregistered)

    Formatting your display in the report itself it for wimps....

    Formatting in your Stored Proceduce is for sissies....

    Real men format the data right in the database columns.

    Sadly, I inherited an Employee Directory that does just that, instead of the ASP webpages saying

    Column1 &" - " Column2

    The previous guy (that was fired) actually put " - " as a prefix of the data in column2!!!!!

  • UncleMidriff (cs) in reply to Jeff S
    Jeff S:
    By the way, note the very beginning of the SELECT statement:

    SELECT DISTINCT 

    In a select with joins and calculations, if it starts with SELECT DISTINCT, that tells you right off the bat to stop reading and re-design either the SELECT from scratch, and/or the entire database.

    We intend to do the later, but the people I am doing this for keep coming up with all these little emergencies (a tribute to the system's design) with the current system that we're having to dip into the funding for the redesign just to stay on top of keeping the current system afloat

  • (unregistered) in reply to phpkid
    phpkid:
    <font face="Verdana" size="2">What kind of table name ' S_C_S_L' is?</font><font face="Verdana">

    <font size="2">JD</font></font>


    Probably the genius idea of the dumb fucking faggot that created this, thinking that it was the best way to describe the table in a way that isn't confusing. I'm suprised the retard didn't prepend it with tbl, and name each field something like colCMewr, colCMa_c_l, etc, which would have made more sense. It would have been a better idea to just put all the data in one table too.
  • Blue (cs) in reply to

    Nice post, anonymous above me.  No further comment on that.

    Regarding the way-earlier anonymous post relating to sprocs that many responded to, my sarcasm detector actually went off this time!  I really don't think they were serious about it being a good idea for sprocs to format the data for reports, etc.

     

  • (unregistered)

    oh my god, I do believe Blue is sharpening his wit [;)]

  • Bustaz Kool (cs) in reply to
    :

    This is the primary reason why I don't ever use stored procedures.  I've had nothing but bad experiences with them.

    - Joshua

    Betty, Do I have an interview scheduled with anyone named "Joshua"?  If so, could you cancel it, please?  We won't be needing his services...

  • brandonh6k (cs) in reply to Bustaz Kool
    Bustaz Kool:
    [image]  wrote:

    This is the primary reason why I don't ever use stored procedures.  I've had nothing but bad experiences with them.

    - Joshua

    Betty, Do I have an interview scheduled with anyone named "Joshua"?  If so, could you cancel it, please?  We won't be needing his services...

    ROFL

    That was my thought too... [:P]

  • (unregistered) in reply to
    Anonymous: "Formatting at the on the report"?  Who would do that?  I don't know, since it got lost in transation.
     
    And to the other anonymous:  Don't use Faggot as a general put-down.  Some of us are gay.
     
    David Walker
  • (unregistered)

    In a select with joins and calculations, if it starts with SELECT DISTINCT, that tells you right off the bat to stop reading and re-design either the SELECT from scratch, and/or the entire database.

    Not always, but if I have a specific reason for using DISTINCT, I'll include a comment to explain it.  If you don't see an explanation (or if the explanation doesn't make sense, or if it returns more than a couple of fields), though, it probably means they don't understand their join.

    As for the stored procedure returning formatted data, the stored procedure might not be what they're trying to reuse.  I'm working on a system right now where the procedure returns formatted data so that I can use the same Crystal Reports file for a dozen different reports.

  • Jeff S (cs) in reply to
    :

    As for the stored procedure returning formatted data, the stored procedure might not be what they're trying to reuse.  I'm working on a system right now where the procedure returns formatted data so that I can use the same Crystal Reports file for a dozen different reports.

    That statement makes no sense at all ... whether or not other processes are using that stored proc is no reason to force formatted at the database layer.  The database should return DATA, not a bunch of columns converted to varchars for presentation purposes. 

  • (unregistered) in reply to Jeff S

    That statement makes no sense at all ... whether or not other processes are using that stored proc is no reason to force formatted at the database layer.  The database should return DATA, not a bunch of columns converted to varchars for presentation purposes.

    Right, whatever.  The reason you don't normally want formatted data coming out of the stored procedure is that it interferes with the reusability, because a different application probably wants different formatting.  In this case, there was a choice between a reusable procedure (when I had no plans to reuse it) and a reusable Crystal Report, which I needed to reuse immediately.

    Granted, I'm probably a little biased because I like working in SQL, and Crystal Reports makes me want to claw my eyes out.

  • Jeff S (cs) in reply to

    :


    Right, whatever.  The reason you don't normally want formatted data coming out of the stored procedure is that it interferes with the reusability, because a different application probably wants different formatting. 

    Uh .... no .... the reason is because that is not what the data layer is supposed to do. It is supposed to return DATA.  If your report receives nothing but varchar's like "$2,344.00" and "December 23, 2005" then it cannot sort or total those values or extract date part's without first converting BACK to the native datatypes that should have been returned in the first place! 

    (If you want to have a discussion, please sign up for an account or at least put a signature at the end of your posts...)

     

  • PstScrpt (cs) in reply to Jeff S

    (The reports are all on telephone call data, so that the explanation makes sense.)

    The Crystal Report calls the procedure, and gets back a recordset containing CallType, FirstGroupData, SecondGroupData and ThirdGroupData, all as VarChar, and Calls, Minutes and Price as numeric fields.  Sorting is done in the procedure, and Crystal handles formatting the numbers and keeping totals.

    What's actually in the FirstGroupData, SecondGroupData and ThirdGroupData fields varies widely, but the report doesn't care.

  • Chris (unregistered) in reply to
    Anonymous:

    This is the primary reason why I don't ever use stored procedures.  I've had nothing but bad experiences with them.

    - Joshua

    ...from a future contributor

  • Chris (unregistered) in reply to Chris
    Anonymous:
    Anonymous:

    This is the primary reason why I don't ever use stored procedures.  I've had nothing but bad experiences with them.

    - Joshua

    ...from a future contributor

    ...I guess that's what I get for using Opera.  Shame on me.

  • TechNoFear (cs) in reply to loneprogrammer

    loneprogrammer:
    All you have to do is use replace(text, ".", "")

    Yes, but you are totally missing the point.  Why should this guy's code have to clean up the mess made by the first guy's code?

    One good WTF does not deserve another.

     

    couldn't you just write a method to inherit his and use replace(text, ".", "") in yours ?

     

     

     

    lmao :p

  • jmk (unregistered) in reply to loneprogrammer
    loneprogrammer:
    All you have to do is use replace(text, ".", "")

    Yes, but you are totally missing the point.  Why should this guy's code have to clean up the mess made by the first guy's code?

    One good WTF does not deserve another.



    What do you mean that you shouldn't have to clean up someone else's code?  That's about 90% of programming jobs.  If all you do is write brand new code, you're lucky.
  • FredSaw (cs) in reply to phpkid

    phpkid:
    <FONT face=Verdana size=2>What kind of table name ' S_C_S_L' is?</FONT><FONT face=Verdana>

    <FONT size=2>JD</FONT></FONT>

    A periodic table?

  • wow gold (unregistered)
    Comment held for moderation.

Leave a comment on “Payback is Still Hell”

Log In or post as a guest

Replying to comment #:

« Return to Article