• (nodebb)

    This article gets an A and the code gets an F.

  • Jajcus (unregistered)

    Even if those were stored and compared as a numbers… why is 2.99999999 (or 2.999999995) considered 'A' while 2.0 is 'D' and 3.0 is 'C'?

  • RLB (unregistered)

    It's not just IT managers who like dashboards; it's users, too. Particularly financial users. Pie chart of most profitable customers? Yes, please! Line graphs of takings, expenses and results over the last three years? Thank you! And in their case, I can see the point.

    (Bonus: writing functions to construct simple, bright SVG and HTML charts is exactly the kind of fiddly thing with an immediately visible result that I enjoy doing.)

  • Chronomium (unregistered) in reply to Jajcus

    Why, obviously the related database field has been limited to numbers of less than 9 (eight digits and one point, of course). 2.99999999 is simply too large a number (in length, not magnitude, who would think otherwise) to store in the related database field. So surely such falling between the cracks cannot ever happen, and would never be abused by tricky and false systems to only modify their score by a sliver yet jump three letter grades.

  • Prime Mover (unregistered) in reply to RLB

    I used to do that sort of thing myself. The first thing I learned was that it didn't matter one bit whether the data in the graphics were accurate or not, what was most important was that they looked pretty.

    Such graphics are not used to provide insight into details of business management. They are used a) to impress the audiences of meetings and b) t provide excuses to fire people who would otherwise make payroll too expensive.

  • Hal (unregistered) in reply to RLB

    What is wrong with a pie chart of most profitable customers? Every place I have ever worked allowed where the greatest contribution margin came from to inform all sorts of decisions. Now obviously you need to understand a little about the data and business behind it to know you are not seeing a outlier - We made 1 million on Initrode this month but they are barely profitable the rest of the time, or Wow Initrode been the bulk of our revenue - well yeah because they have been a client for over a decade but Initech is contributing 4x the monthly revenue but was just signed this year..

    However its always been the case there are lies, damn lies, and statistics. Its the responsibility of the person using the dashboard to have some understanding of data and business associated with it; as to what types of decisions it should inform or actions it should trigger, and if they make decisions without that understanding and don't both asking well ultimately that is their failure. Provided of course the dashboard is correctly calculating what it claims to be (rather unlike the code in the article).

  • (nodebb)

    My first reaction when reading this article was, how do you know the [VALUE] column is a string? The big database platforms handle implicit data type conversion with a data type precedence table. At least for Microsoft SQL Server (which I work with), varchar(string) data is lower precedence than everything except binary, so if the [VALUE] column is one of the number data types, the string values ('1.00001') would be converted to the data type of the [VALUE].

    Turns out that most of this code would work...except for this line: WHEN VALUE = '' THEN '' Converting '' to decimal will fail, so we know [VALUE] can't be decimal. Converting '' to int succeeds (bizarrely, as 0), so that part works, but then converting '1.000001' to int (data type precedence rules) fails.

    Except... If the [VALUE] column is stored as float (TRWTF), converting '' to float also results in 0, converting '1.00001' to float of course results in 1.00001(ish), and this all works just fine.

  • Keld List Laursen (unregistered)

    The SQL isn't that bad. At least not if it is T-SQL. In T-SQL the string data type is waaay down the hierarchy, meaning that SQL server vil try to convert the text to anything higher up, such as dates, floats and so on. This leads to the situations, where if you compare a varchar column to the number 9, you can get the "cannot convert the varchar value 'xxx' to int" The only way that I know to create a datetime, short of constructing it from parts using a lot of DatePart() functions, is to deliver it as a text.

    No, TRWTF here is that they compare with '1.999999999', and says less than, and in the next range they test >=2. This will have all edge cases in between falling into category A.

  • Scott (unregistered)

    As developers, we often have to engage with management who doesn't have a clue.

    Fixed your first sentence.

  • Moist (unregistered)

    Can we stop using the word "slurp?"

  • (nodebb) in reply to Moist

    Can we stop using the word "slurp?"

    If we're talking about consuming Slurpees, "slurp" seems a logical word to use...

  • Carl Witthoft (google)

    Ahhh, you guys have it easy. Here at Vinitrode, we have to report metrics on parameters that are not traceable to anything useful or comprehensible. They just .... exist.

  • Moist (unregistered) in reply to Steve_The_Cynic

    Leasor : Leasee :: Slurpor : Slurpee ??

    I think, within the bounds of programming lingo, "slurp" is being used incorrectly in this article. From what I can tell, it means reading something into memory whole vs. progressively. So, while it's possible, it seems unlikely that the system "slurped data from a dozen different silos" or "slurps giant piles of data from a variety of sources."

  • Barf4Eva (unregistered)

    This looks like total shit to me. Bad formatting is always obnoxious as hell and a time sinker for trying to read it. Old school SQL joins are annoying AF. The mix case of aliases and no aliases is just boneheaded annoyance as well. These are derived tables, and apparently 400 lines of them, perhaps implying 10-20 left joins of this crap. They might not get understood in a correlated sub-query fashion at all, which is a good thing... But... The repeated use of the same tables over and over in each derived table is a god-awful mess that will hit these tables numerous times for what could likely be re-written as a single query. Ripe for refactoring so that other developers don't have to waste their lives away trying to make sense of it or dealing with the the eventual and current performance implications. I give this SQL an F.

  • Anonymous') OR 1=1; DROP TABLE wtf; -- (unregistered)

    I like how there's a different number of 9's in the different cases:

             WHEN  VALUE < '1.9999999999' THEN 'E'
                          ^ 10 9's
         WHEN  VALUE >= '2' and  VALUE< '2.99999999' THEN 'D'
                                           ^ 8 9's
         WHEN  VALUE >='3' and  VALUE < '3.999999999' THEN 'C'
                                           ^ 9 9's
         WHEN  VALUE  >='4' and  VALUE < '4.999999999' THEN 'B'
                                           ^ 9 9's
  • Gusset (unregistered) in reply to Moist

    Slurp.

  • WTFGuy (unregistered)

    @Barf4Eva:

    So would that be an F that dashboards as a red light, or a green light?

    And of course we all already know "dashboard" is now a verb. BizSpeak is like that; forever nounifying their verbs and verbifying their nouns.

  • Loren Pechtel (unregistered)

    There often comes a time when doing it all in SQL is a lot harder than doing a bit of massaging after you get the results. This is a clear example of such a case.

  • (nodebb)

    You just can’t draw a conclusion by quoting a few lines from a “400 lines” piece of code which is a “code” unlike a piece fiction. Your article is unfortunately not clear about what you want to convey. Your following statement:

    “ First, if the VALUE is "20", that's a "D". A value of "100" is going to be an "E" “ is wrong also. Just execute the following and you will see both of these values will give the output as “A” declare VALUE VARCHAR2(90) := '20'; RESULT char(1); begin select case WHEN VALUE = '' THEN '' WHEN VALUE < '1.9999999999' THEN 'E' WHEN VALUE >= '2' and VALUE< '2.99999999' THEN 'D' WHEN VALUE >='3' and VALUE < '3.999999999' THEN 'C' WHEN VALUE >='4' and VALUE < '4.999999999' THEN 'B' ELSE 'A' END into RESULT from dual; dbms_output.put_line(RESULT); end;

    Ideally any piece of code should consist of the ddl and some sample data to make things clear. Any code you are providing should be executable so that the readers can immediately execute it and understand it and benefit from it.

  • (nodebb) in reply to Jajcus

    The following two points could resolve your concern

    1. There is a validation in the data that there must be only 8 digits after the decimal point.
    2. “=” is missing in the code There could be a correction required as follows: WHEN VALUE <= '1.9999999999' THEN 'E' WHEN VALUE >= '2' and VALUE<= '2.99999999' THEN 'D' WHEN VALUE >='3' and VALUE <= '3.999999999' THEN 'C' WHEN VALUE >='4' and VALUE < ='4.999999999' THEN 'B'
  • RLB (unregistered) in reply to Hal

    What is wrong with a pie chart of most profitable customers?

    Nothing, that was my point as well. Yes, dashboards are often simplistic and used in a short-sighted manner, but when done and used well - by financial staff, not by manglers - they're a great tool.

  • Sole Purpose Of Visit (unregistered) in reply to WTFGuy

    "We need you to onboard the dashboarding via waterboarding. Stakeholder all the developerboreds while you're at it."

  • Officer Johnny Holzkopf (unregistered) in reply to Sole Purpose Of Visit

    "Please dashboard the synergy effects for the CEO. He requires management to do management. He believes the manage is yellow at the moment, which is considered non-compilance, and you IT guys and gals need to make him happy again. And do more designering on the web below the fold, the sandwich needs to be more playful. ASAP. X plus 1."

  • Wizofaus (unregistered)

    How is '20' less than '2.9999999'? The period/dot . comes before 0 in almost any collation setting the database is likely to be using.

  • BOFH (unregistered)

    Well, at least in Oracle, you can weite numeric valudes as text. This is one of my pet peewees - several of my co-sufferers are using this style for no apparent reason.

Leave a comment on “A Dash of SQL”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article