• my name (unregistered)

    as long as there are no cursors involved, everything is fine

  • (nodebb)

    I'm going to come back to what I've mentioned before: you can't computerise existing manual business processes, because of stuff like those accounting codes being different (or the same but used for different things, which is probably worse) in each department.

    What you do is you mutate those processes so that they become, in essence, an informatic process done manually, and then you let the computer run the process.

  • (nodebb)

    This looks like it was done by one of those "Query Builders", which rarely create anything human-readable.

    Then again... as long as it works and runs in reasonable time then is there actually a problem?

  • Network Noadle (unregistered)

    This is reminiscent of the SQL code generated inside Sage 200: gives me shivers looking at it. That said, it was trivial (if time consuming) to strip out much of the cruft from the S200 query to get down to brass tacks.

  • NotAThingThatHappens (unregistered)

    Actually, Red-Gate's "SQL prompt " (*) can format this query quite well.

    Its a query that just does 10 unions, i've seen worse.

    *) Not affiliated, just a good tool.

  • Wyatt (unregistered)

    What, no mention of the WITH(NOLOCK) sprinkled generously throughout the query?

  • Richard Brantley (unregistered)

    It goes on, and on, and on...

  • (nodebb) in reply to Straw Manninen

    This looks like it was done by one of those "Query Builders", which rarely create anything human-readable.

    All the WITH(NOLOCK) sprinkled in there make it highly unlikely that software wrote this query.

  • amorphis (github) in reply to Wyatt

    ^^ this!

    They have a lot of nerve querying for "transactions" when they're perfectly willing to read uncommitted data LMAO

    also, it smacks of using your OLTP system as a reporting environment...

  • (nodebb)

    I hate that our job has something that might be just as bad, and it's for a billing report.

    When I did a simple format, this query is simpler than ours because the main query is just SELECT sum(tax_paid) AS Tax_Paid FROM (subquery) AS Taxes, while ours (I haven't had a reason to touch it myself) has MULTIPLE columns, and it runs for like an hour.

  • ooOOooGa (unregistered)

    Note the horizontal scrolling on today's code, there was no good place for me to add linebreaks for readability.

    Yes there is. Break on the nine UNION ALL clauses and the twenty-eight LEFT JOIN clauses instead of breaking after random AND condition linkings.

    It is still a huge WTF. You will still have horizontal scroll bars. But it will at least help to inform about the structure of the query.

  • Ollie Jones (unregistered)

    Just think what this query will look like a century from now!

  • (nodebb)

    OK I give up - just put the data into a VARCHAR(MAX) column as pipe-separated text lines - reading that would be simpler than the "query" in the article.

  • (nodebb) in reply to Steve_The_Cynic

    What you do is you mutate those processes so that they become, in essence, an informatic process done manually, and then you let the computer run the process.

    Absolutely not! Everybody needs to do what they've always done - on paper - and continue to do it all on paper and then enter it into "the computer" so the Finance Wizards in the Ivory Tower can run some magic report to see when they can afford to take over the competition or perhaps cure cancer.

    But, the "real" accounting.... that all takes place in the bottom drawers of people's desks where all the paper is filed in a perfectly organized manner that makes no sense to anyone, not even the person who "owns" it.

  • Gallowglass (unregistered) in reply to Ross_Presser

    gonna have to disagree with you. CUIC, Siebel, OBIEE are all examples I've personally dealt with where the auto generated sql gets religiously peppered with nolock garbage. in fact, when I saw this I immediately assumed it must've been auto generated BECAUSE of the with (nolock) garbage. So my experience is directly polar to yours.

  • WTFGuy (unregistered)

    @SteveTheCynic ref

    I'm going to come back to what I've mentioned before: you can't computerise existing manual business processes, because of stuff like those accounting codes being different (or the same but used for different things, which is probably worse) in each department.

    What you do is you mutate those processes so that they become, in essence, an informatic process done manually, and then you let the computer run the process.

    I've been preaching that since 1974. It isn't working. :(

  • Dave K (unregistered)

    Putting this code through a SQL formatter makes it at least readable, but no less of a WTF. The fact that there are tables named transactions and other_transactions just screams for a db redesign. And the query contains numerous LEFT JOINS to the sale_invoices table that, as far as I can tell, are not needed since no columns from sale_invoices are ever referenced in the SELECT, WHERE, or in other JOIN clauses.

  • Jay (unregistered)

    I decided to throw it into a SQL formatter so i could get a better grasp on it visually. This is 10 different sub-queries UNION ALL-ed together! The first 4 are "Transactions" and "Other_Transactions" split between December 20 and 21st, 2013 and prior to December 20th, 2013. The next 2 negate some transactions based on the "Split_payments" table. The rest are other "special" transaction types which I'm not sure I'm qualified to understand.

    The first 4 need to be split because one has a quantity multiplier and the other doesn't.

    It looks like poor definitions of how to handle transactions caused multiple people to store and handle transactions in different ways at different time, but the "Transaction_types" might be more complex. Still not good code. Anything this complex is no longer a simple query but a script which needs comments and separation for readability.

  • tbo (unregistered)

    Here's my favorite part:

    WHERE t.Transaction_Date BETWEEN '2013-12-20' AND '2013-12-21' AND t.Transaction_Time BETWEEN '2013-12-20 06:00:00' AND '2013-12-21 05:59:59'

    Also: 5 captchas? Am I the robot?

  • Carl Witthoft (google)

    If there's the SQL equivalent of the "Obfuscated C code" contest, this could be munged into a great entry.

  • Abigail (unregistered)

    Formatting the query to something readable isn't hard. And if you do, you see the query is quite repetitive, and not that hard to read: https://gist.github.com/Abigail/f997fdf43b83a3441e1e66601f8a94a6

  • (nodebb) in reply to WTFGuy

    I've been preaching that since 1974. It isn't working. :(

    The phrasing of my comment was based on a history of LEO (Lyons Electronic Office) that I read some years back. However, it's probably unobvious why I bothered to read this history... Let's just say that in the first half of the 1960s, my mother signed on to work for LEO, and rose to the rank of Chief Programmer, before (as expected in the 1960s) resigning because she was pregnant with ... um ... the future Steve_The_Cynic. If you read those histories, you may run across the name "Freeman's"... In, roughly speaking, 1974, maybe 1975, I went, for reasons that are mostly lost in the mists of time, but related to LEO, to London with my father and we ended up having lunch in Freeman's staff cafeteria/canteen.

  • Peter D (unregistered)

    I was going to write that this isn't bad, but no this is awful. This does have the virtue of being able to see what is going on (the reversed flag and the "-1 * SUM()" clauses point up some conditions), but getting report information needs to be much clearer.

  • WTFGuy (unregistered)

    Two contrasting thoughts, neither very encouraging:

    1. Did they do some major change in their transaction codes effective on Dec 20/21, so all of their queries now have doubled logic to UNION the results of querying old fashioned codes with old fashioned business rules and querying new-fangled codes with the new-fangled business rules?

    2. Do they routinely generate a new query like this with hard coded dates for every weekend? I can just imagine the stored proc store loaded with hundreds of entries like

    sp_QueryTaxTransactions_date_2021_10-12
sp_QueryTaxTransactions_date_2021_10/17
sp_QueiryTaxTransactios_date_1017-2021
sp_Quierytransactionstaxable_for_Bob_for_2021_28_10

    etc., for hundredsof rows.

  • Staticsan (unregistered)

    Look, this is the sort of thing I love to fix, but almost never have the authority to actually fix properly.

    Yes, the SQL is gnarly, but not unformattable (that's probably the real WTF here). The query itself could probably be simplified with some clever Views or large sub-Selects, though quite how will depend on the engine. Some databases do their best work with nested queries, others do best with Joins, still others shine best with explicit temporary tables. There are also tricks you can do with CASE to collapse some of those sub-Selects down. I know SQL kind of encourages copy-and-paste but there are ways to do broader queries to catch everything before you wrap it in another sub-Select to filter things out.

    And all that's before you consider how to alter the tables and how the code uses them.

  • (nodebb)

    Why the hate re .. WITH (NOLOCK) .. ?

    Assuming (butt u me, haha) that this monster does not run at/on 2013-12-20 but some time later and it is quite expensive: why should one lock n+1 tables for the duration and possibly cause stalls (..cannae INSERT into sometable(...) VALUES (...) 'cos sometable is locked by ...) elsewhere?

  • (nodebb)

    Attempted some pretty-printing but it remains ugly as... at least it reveals kind of a structure.

    https://pastebin.com/Zh0Dbd4q

    I lost track somewhere in the middle where it apparently became inconsistent. It's too twisted to be just generated. But probably assembled from multiple individual statements from different sources. There is no consistency whatsoever. One of my favorites is this repeated condition (throughout all of the SELECT statements):

    WHERE t.Start_Date BETWEEN '2013-12-20' AND '2013-12-21' AND t.Start_Time BETWEEN '2013-12-20 06:00:00' AND '2013-12-21 05:59:59'

    Man, why??? Why a redundant date-only field when there is another DateTime field available? Why is 06:00:00 the cutoff time? If there's a Start_Date / Start_Time, what about Finish_Date and Finish_Time? Shouldn't they cover the same time frame?

  • (nodebb)

    My sql formatter extension blew up trying.

  • WTFGuy (unregistered)

    @** JoeMS2018** ref:

    Why is 06:00:00 the cutoff time?

    Because 0600 in UTC is midnight in the midwest US where corporate HQ probably is, at least during non-DST which is the case in December. At least in 2021, if perhaps not in 2023.

    Of course if they have branches in different timezones, then maybe the hard-coded 6h00 offset is not correct for those. And we know it won't be correct for the equivalent query dated during the summer.

    So many potential gotchas, so little correctness.

  • Laguer (unregistered) in reply to amorphis

    By transactions, they mean something written to the ledger, not something with begin tran. And yes ... It's something to use uncommitted data to make it run faster or something like Lguer

  • anypropertyforcash (unregistered)
    Comment held for moderation.
  • maalik (unregistered)
    Comment held for moderation.
  • maalik (unregistered)
    Comment held for moderation.
  • Ivan (unregistered)

    The 10 Most Scariest Things About Porn Star Porn star

Leave a comment on “Fetching Transactions”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article