Comment On Payback is Still Hell

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

What kind of table name ' S_C_S_L' is?



JD

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, ".", "")

The real WTF is using Access for reporting.. 



Crystal Reports for life!

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.

phpkid:

What kind of table name ' S_C_S_L' is?

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...

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 about 80 bajillion
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. 

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

Bustaz Kool:

phpkid wrote:

What kind of table name ' S_C_S_L' is?

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.

 

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.

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
    ELSE 0
END AS QUARTER

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????

 

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!

:

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."

:

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? 

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.

DATEPART(q, S_C_S_L.B_D) would be to easy I think[;)]

:

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
    ELSE 0
END AS QUARTER

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.

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;



+----+

| x  |

+----+

| 1  |

| 0  |

| -1 |

+----+



Neat.

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.

:

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
    ELSE 0
END AS QUARTER

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).

:

DATEPART(q, S_C_S_L.B_D) would be to easy I think

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

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

Drak:

wrote:

DATEPART(q, S_C_S_L.B_D) would be to easy I think

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.

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

- Joshua

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.

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!!!!!

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

phpkid:

What kind of table name ' S_C_S_L' is?



JD

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.

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.



 

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

:

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...

Bustaz Kool:

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]

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.

:

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. 

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.

:

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...)

 

(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.

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

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.

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

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.

phpkid:

What kind of table name ' S_C_S_L' is?

JD

A periodic table?

Out of runes of magic gold? Need it in urgent? Yes, I can understand you. As the most important currency, without rom gold, you ever can’t do anything. So you need to buy rom gold from those most professional and loyal game online shops with years’ experience and have a good reputation among players. Is there any difficult? No, when you need the rom gold, please feel free to contact us, we are promising to offer you the cheap runes of magic gold with fastest delivery. Moreover, we are online 24/7, you can contact us any time with any question about. So why are you still irresolute? Come here to grab your cheap runes of magic gold now. Crazy about running warhammer gold? Yup, it is so crucial indeed for us in Warhammer Online. Without it, we can even do nothing, without money to buy items, weapons and so on. So enough warhammer gold is substantial.