• (disco)
    SELECT 
             /*+ optimizer_features_enable('9.2.0') */ 
             n1.order_id           AS order_id, 
             n1.n1_status_id       AS n1_status_id, 
             n1.sku_id             AS sku_id, 
             o.site_id             AS site_id, 
             o.order_dt            AS order_dt, 
             o.d_total_price       AS price, 
             rs.legal_name         AS reseller_legal_name, 
             prs.legal_name        AS parent_reseller_legal_name, 
             s.company_name        AS site_company_name, 
             o.order_state_id      AS order_state_id, 
             item.country          AS item_country, 
             e.country             AS e_country, 
             c.cust_service_region AS region, 
             rs.partner_level, 
             o.followup_dt, 
             n1.approver_confirm_dt, 
             'N'                                                                                    AS reissue_ind,
             Sign(Nvl(Sum(DISTINCT(r.order_id * ( -(Sign(Nvl(r.override_person_id, 0)) -1)) )), 0)) AS rsrc_ctrl_violation, 
             Max(Decode( p.email_address, :1, 'Y', 'N' ))                                           AS manual_approval_ind,
             Max(Nvl(r.resource_ctrl_type_id, 0))                                                   AS resource_ctrl_type_id ,
             Nvl(cfa.poll_status, 'N/A')                                                            AS poll_status ,
             cfa.approve_method 
    FROM     orders_n1 N1, 
             orders O, 
             enterprise E, 
             enterprise RS, 
             enterprise PRS, 
             site S, 
             orders_rsrc_ctrl R, 
             person P, 
             country C, 
             site_item ITEM , 
             c ERT_FILE_APPROVE_DV_T cfa 
    WHERE    n1.order_id = o.order_id 
    AND      n1.n1_status_id <> 3 
    AND      n1.n1_status_id <> 10 
    AND      o.cust_enterprise_id = e.enterprise_id 
    AND      o.reseller_enterprise_id = rs.enterprise_id 
    AND      rs.reseller_referred_by_ent_id = prs.enterprise_id 
    AND      o.site_id = s.site_id 
    AND      o.order_id = r.order_id(+) 
    AND      o.order_complete_ind = 'N' 
    AND      o.order_id = item.order_id 
    AND      item.item_id = cfa.item_id (+) 
    AND      ( 
                      item.country IS NOT NULL 
             AND      c.country_cd = item.country 
             OR       item.country IS NULL 
             AND      c.country_cd = e.country) 
    AND      o.order_dt >= to_date(:2, 'mmddyyyyhh24miss') 
    AND      o.order_dt <= to_date(:3, 'mmddyyyyhh24miss') 
    AND      bitand(nvl(rs.enterprise_type_bitmask, 0), 32) = 0 
    AND      c.cust_service_region = :4 
    AND      ( 
                      p.person_id = 
                      ( 
                             SELECT max(person_id) 
                             FROM   contact_site cs 
                             WHERE  cs.site_id = o.site_id 
                             AND    cs.contact_type_id = decode( n1.sku_id , 5, 19 , 10, 20 , 11, 21 , 15, 25 , 36, 29 , 38, 33 , 40, 37 , 42, 41 , 44, 45 , 46, 65 , 140, 61 , 179, 69 , 185, 73 , 191, 77 , 310, 117 ))
             OR       p.person_id = 
                      ( 
                             SELECT person_id 
                             FROM   order_contact_t 
                             WHERE  order_id = o.order_id 
                             AND    contact_type = 'COMPANY_APPROVER') 
             OR       ( 
                               n1.sku_id = 191 
                      AND      p.person_id = 
                               ( 
                                      SELECT max(person_id) 
                                      FROM   person)) ) 
    AND      o.d_primary_sku_id <> 220 
    GROUP BY n1.order_id, 
             n1.n1_status_id, 
             n1.sku_id, 
             o.site_id, 
             o.order_dt, 
             o.d_total_price, 
             rs.legal_name, 
             prs.legal_name, 
             s.company_name, 
             o.order_state_id, 
             item.country, 
             e.country, 
             c.cust_service_region, 
             rs.partner_level, 
             o.followup_dt, 
             n1.approver_confirm_dt, 
             cfa.poll_status , 
             cfa.approve_method 
    UNION 
    SELECT 
             /*+ optimizer_features_enable('9.2.0') */ 
             n1.order_id              AS order_id, 
             n1.n1_status_id          AS n1_status_id, 
             n1.sku_id                AS sku_id, 
             o.site_id                AS site_id, 
             o.order_dt               AS order_dt, 
             o.d_total_price          AS price, 
             rs.legal_name            AS reseller_legal_name, 
             prs.legal_name           AS parent_reseller_legal_name, 
             s.company_name           AS site_company_name, 
             o.reissue_order_state_id AS order_state_id, 
             item.country             AS item_country, 
             e.country                AS e_countr y, 
             c.cust_service_region    AS region, 
             rs.partner_level, 
             o.followup_dt, 
             n1.approver_confirm_dt, 
             'Y'                                                                                    AS reissue_ind,
             sign(nvl(sum(DISTINCT(r.order_id * ( -(sign(nvl(r.override_person_id, 0)) -1)) )), 0)) AS rsrc_ctrl_violation, 
             max(decode( p.email_address, :5, 'Y', 'N' ))                                           AS manual_approval_ind,
             max(nvl(r.resource_ctrl_type_id, 0))                                                   AS resource_ctrl_type_id ,
             nvl(cfa.poll_status, 'N/A')                                                            AS poll_status ,
             cfa.approve_method 
    FROM     orders_n1 n1, 
             orders o, 
             enterprise e, 
             enterprise rs, 
             enterprise prs, 
             site s, 
             orders_rsrc_ctrl r, 
             person p, 
             country c, 
             site_item item , 
             item_file_approve_dv_t cfa 
    WHERE    n1.order_id = o.order_id 
    AND      o.cust_enterprise_id = e.enterprise_id 
    AND      o.reseller_enterprise_id = rs.enterprise_id 
    AND      rs.reseller_referred_by_ent_id = prs.enterprise_id 
    AND      o.site_id = s.site_id 
    AND      o.order_id = r.order_id(+) 
    AND      o.order_id = item.order_id 
    AND      item.item_id = cfa.item_id (+) 
    AND      item.item_status_id = 5 
    AND      ( 
                      item.country IS NOT NULL 
             AND      c.country_cd = item.country 
             OR       c ert.country IS NULL 
             AND      c.country_cd = e.country) 
    AND      item.start_dt >= to_date(:6, 'mmddyyyyhh24miss') 
    AND      item.start_dt <= to_date(:7, 'mmddyyyyhh24miss') 
    AND      bitand(nvl(rs.enterprise_type_bitmask, 0), 32) = 0 
    AND      c.cust_service_region = :8 
    AND      ( 
                      p.person_id = 
                      ( 
                             SELECT max(person_id) 
                             FROM   contact_site cs 
                             WHERE  cs.site_id = o.site_id 
                             AND    cs.contact_type_id = decode( n1.sku_id , 5, 19 , 10, 20 , 11, 21 , 15, 25 , 36, 29 , 38, 33 , 40, 37 , 42, 41 , 44, 45 , 46, 65 , 140, 61 , 179, 69 , 185, 73 , 191, 77 , 310, 117 ))
             OR       p.person_id = 
                      ( 
                             SELECT person_id 
                             FROM   order_contact_t 
                             WHERE  order_id = o.order_id 
                             AND    contact_type = 'COMPANY_APPROVER') 
             OR       ( 
                               n1.sku_id = 191 
                      AND      p.person_id = 
                               ( 
                                      SELECT max(person_id) 
                                      FROM   person)) ) 
    AND      o.d_primary_sku_id <> 220 
    GROUP BY n1.order_id, 
             n1.n1_status_id, 
             n1.sku_id, 
             o.site_id, 
             o.order_dt, 
             o.d_total_price, 
             rs.legal_name, 
             prs.legal_name, 
             s.company_name, 
             o.reissue_order_state_id, 
             item.country, 
             e.country, 
             c.cust_service_regi 
    ON, 
             rs.partner_level, 
             o.followup_dt, 
             n1.approver_confirm_dt, 
             cfa.poll_status , 
             cfa.approve_method 
    ORDER BY n1_status_id, 
             order_dt DESC
    

    …yeah, that didn't really make it any better :laughing:

    INB4 you did that manually

  • (disco)

    20,000 fields under the select.

  • (disco) in reply to RaceProUK

    Next step: ANSI joins.

  • (disco)

    I readily admit that I know just enough SQL to be dangerous, but there are a couple of specific things, beyond the overall complexity, that make me wonder if they are WTFs:

    FROM     orders_n1 N1, 
             orders O, 
             enterprise E, 
             enterprise RS, 
             enterprise PRS, 
    

    Why is the enterprise table used three times with different aliases?

    GROUP BY n1.order_id, 
             n1.n1_status_id, 
             n1.sku_id, 
             o.site_id, 
             o.order_dt, 
    ...
    

    Obviously, I don't know their data, but wouldn't/shouldn't the order_id be unique, rendering the rest of the terms in the GROUP BY clause superfluous?

    Somebody please kill those magic numbers. WTF is special about n1_status_ids 3 and 10, or all those contact_type_ids?

    BTW, @RaceProUK, that really did help a lot.

  • (disco) in reply to HardwareGeek
    HardwareGeek:
    Why is the enterprise table used three times with different aliases?

    From the WHERE, E is the customer's enterprise, rs is the reseller's enterprise, and prs is the reseller's referrer.

  • (disco) in reply to HardwareGeek
    HardwareGeek:
    Why is the enterprise table used three times with different aliases?

    They're different things:

    AND      o.cust_enterprise_id = e.enterprise_id 
    AND      o.reseller_enterprise_id = rs.enterprise_id 
    AND      rs.reseller_referred_by_ent_id = prs.enterprise_id 
    

    Customer, reseller, referrer (apparently). This is a major reason why you should always use ANSI joins:

    join enterprise E on  o.cust_enterprise_id = e.enterprise_id 
    join enterprise RS, on o.reseller_enterprise_id = rs.enterprise_id 
    join enterprise PRS on rs.reseller_referred_by_ent_id = prs.enterprise_id 
    

    It makes it clearer how the table is being used and makes it more difficult to forget to specify the join condition / spot missing ones. I've had to clean up retarded queries from multiple cow-orkers that made mistakes because they used those old style joins. :facepunch:

    Also: :hanzo:d

  • (disco) in reply to HardwareGeek
    HardwareGeek:
    Obviously, I don't know their data, but wouldn't/shouldn't the order_id be unique, rendering the rest of the terms in the GROUP BY clause superfluous?

    You have to put anything that isn't an aggregate function (sum, max, etc) in the GROUP BY clause.

  • (disco) in reply to boomzilla

    Depends on your database engine. I know oracle requires that, but mysql does not.

  • (disco) in reply to PleegWat

    Eh...fair enough.

  • (disco) in reply to boomzilla
    boomzilla:
    This is a major reason why you should always use ANSI joins: ``` join enterprise E on o.cust_enterprise_id = e.enterprise_id join enterprise RS, on o.reseller_enterprise_id = rs.enterprise_id join enterprise PRS on rs.reseller_referred_by_ent_id = prs.enterprise_id ``` It makes it clearer how the table is being used and makes it more difficult to forget to specify the join condition / spot missing ones.

    Ah, that makes sense. You're (well, not you; the idiot that wrote that query) doing three separate joins to get three separate sets of rows. One might misread the other form of the query to think you were getting one set of rows that satisfied all three conditions; I did.

  • (disco)

    To me, the big deal is all those subqueries and the UNION. If you really want to slow things down, that can be a very effective way to do it.

  • (disco)

    Bit-masking in SQL? :wtf:

  • (disco) in reply to dkf

    http://www.postgresql.org/docs/8.0/static/functions-bitstring.html

  • (disco)

    I work with worse on a daily basis, sadly. It's such a treat to deal with massive stored procedures that take an "object ID" as a parameter and do different selects, calls and inserts based on said object ID, which later is read by a SQL job from a table which does other things based on the object ID and other parameters. It's an entire application built in SQL so it can be automated.

  • (disco)

    Honestly, it looks pretty reasonable once formatted. I would have personally moved the person selector in a subquery in the from clause.

    Speed: It might be slow for a number of reasons (indexes, partitions, sort space, statistics, optimizer, etc.), but the query looks reasonable. If you have worked with complex data models and need to make reports that's how SQL looks (and this one doesn't include anything really fancy like hierarchical clauses).

    ANSI JOIN: NO! They look nice and work for simple models but when you start making complex joins, or develop complex queries, you stop using them: it makes the code unreadable or unworkable. The key is properly naming your alias.

  • (disco)

    This isn't really funny. :wink:

    I had a job once where I had to deal with a query like this. It wasn't fun at all.

  • (disco)

    Except for the magic numbers and the person_id selector subquery (and MAYBE the lack of ANSI joins), I can't see an issue with this query. Even the what.thedailywtf.com queries would look worse than this if formatted into one line

    If anyone doesn't agree, feel free to show me your improved version

  • (disco) in reply to g0uy0u
    g0uy0u:
    **ANSI JOIN: NO!** They look nice and work for simple models but when you start making complex joins, or develop complex queries, you stop using them: it makes the code unreadable or unworkable. The key is properly naming your alias.

    Hey, @antiquarian, I think we have a live one!

  • (disco)

    I once had to fix a 1250 line report query. After trying a few hours, I rewrote it.

  • (disco) in reply to Vault_Dweller
    Vault_Dweller:
    Even the what.thedailywtf.com queries would look worse than this if formatted into one line

    You don't write nontrivial queries on one line. Though I agree if this came out of the DB layer's debug statements, intermediate newlines may have been outside the query text, and thus already gone.

  • (disco) in reply to boomzilla
    boomzilla:
    Hey, @antiquarian, I think we have a live one!

    Deliverance?

  • (disco) in reply to PleegWat

    There are a whole host of reasons how this query could have ended up in one line on the front page, I doubt it is how the original query looked. But I agree, if it was the case, it would be TRWTF

  • (disco) in reply to boomzilla
    boomzilla:
    g0uy0u:
    **ANSI JOIN: NO!** They look nice and work for simple models but when you start making complex joins, or develop complex queries, you stop using them: it makes the code unreadable or unworkable. The key is properly naming your alias.

    Hey, @antiquarian, I think we have a live one!

    Just a standard Front Page Troll™. Nothing to see here, please move along.

  • (disco) in reply to antiquarian

    No trolling, simply years of experience.

    ANSI joins work fine when your query is structured like a simple tree, but when your join conditions are graph like, you end jumping all over the place to try to make sense of what is going on.

  • (disco)

    Yeah, this is really not so bad. Some of the operations could have been done before/after, but doing everything in a single SQL guarantees read consistency without the headache of keeping what could potentially become a much longer running transaction hanging out there. Just because an operation has a lot of words in it doesn't mean its a bad one.

    As for the aliases, I do wish they'd spent the extra bytes and used words like referring_enterprise instead of PRS. Makes a lot of difference to the readability of any query.

  • (disco) in reply to g0uy0u
    g0uy0u:
    No trolling

    They all say that.

  • (disco) in reply to g0uy0u
    g0uy0u:
    ANSI joins work fine when your query is structured like a simple tree, but when your join conditions are graph like, you end jumping all over the place to try to make sense of what is going on.

    Not using ANSI joins mean that you're going to be jumping all over the place to make sense of what's going on regardless of your conditions.

  • (disco) in reply to boomzilla

    It's the query writing equivalent of application based referential integrity.

  • (disco)

    Ah that's a tiny query - I look after a bunch of legacy Access applications (yes, I know) where the original developer decided it was a good design decision to have numerous 300-line queries (full of subqueries and unions) stored in tables and executed via VBA when required.

  • (disco) in reply to boomzilla

    Which is why I said before: you need to properly name your aliases. When you have 20-30 lines of join conditions you get to read them on a single screen page.

    You have also to take into account that I'm talking about complex SQL: not simply joining a few tables, but also some subqueries.

  • (disco) in reply to g0uy0u
    g0uy0u:
    Which is why I said before: you need to properly name your aliases

    I don't give a shit how well or poorly you named your aliases.

    g0uy0u:
    When you have 20-30 lines of join conditions you get to read them on a single screen page.

    Yes, whether they're next to the table or not. I'm not sure what your point is.

    g0uy0u:
    You have also to take into account that I'm talking about complex SQL: not simply joining a few tables, but also some subqueries.

    Why should that matter? I use stuff like that all the time. Now I just think you like to make things difficult for yourself.

    That said, I've used non-ANSI joins in some situations where they actually made things easier, but those have been very rare. Separating your joins from their conditions is just asking for trouble, IME.

  • (disco)

    http://www.tailgatingideas.com/wp-content/uploads/2009/01/math_cartoon.jpg

  • (disco) in reply to RaceProUK

    Yeah, once formatted it's not so horrible. Not pretty, but I've seen much worse. The query definitely needs ANSI joins, that old-school Oracle join syntax has got to go (yes, I know, they defined it before ANSI, but that was decades ago). There are a couple of pieces that jump out as particularly evil, though. Whatever the hell is going on here...

    Sign(Nvl(Sum(DISTINCT(r.order_id * ( -(Sign(Nvl(r.override_person_id, 0)) -1)) )), 0)) AS rsrc_ctrl_violation,

    ...that logic could be pulled out into a view. And that DECODE with the long list of magic numbers is a disaster.

  • (disco) in reply to g0uy0u
    g0uy0u:
    **ANSI JOIN: NO!** They look nice and work for simple models but when you start making complex joins, or develop complex queries, you stop using them: it makes the code unreadable or unworkable. The key is properly naming your alias.

    As someone who's written an eight-way inner join on subqueries, including a unioned, joining subquery, I'd rather have the ANSI join syntax, TYVM.

    Also, try to write an outer join without ANSI syntax in a non-Orrible database. ;)

    silentd:
    There are a couple of pieces that jump out as particularly evil, though. Whatever the hell is going on here...

    Sign(Nvl(Sum(DISTINCT(r.order_id * ( -(Sign(Nvl(r.override_person_id, 0)) -1)) )), 0)) AS rsrc_ctrl_violation,

    ...that logic could be pulled out into a view. And that DECODE with the long list of magic numbers is a disaster.

    Yeah, if they could get their heads out of the Oracle 9 days, they could put those in virtual columns....

    Still not as :wtf: as the recursive query I once experimented with to "unpack" CLOBs of comma-separated child record IDs into 1 row per child record ID, though -- that one was scrapped in favor of doing the job in C++ though because it was just too dang slow, despite the C++ code having to make a bunch of network round trips to talk to the DB.

  • (disco)

    I've seen some pretty huge ones, but our huge ones tend to be in the data processing logic, with 100-200 columns and 2-3 unions across database links. Anonimized, slightly, from the comments saying what's going on:

    INSERT /*+ append */ INTO dst (columns)
    SELECT columns FROM src WHERE filters GROUP BY columns UNION ALL ...
    UNION ALL SELECT columns FROM (
    SELECT columns FROM src WHERE filters GROUP BY columns UNION ALL ...
    ) INNER JOIN (
    SELECT columns FROM src WHERE filters GROUP BY columns UNION ALL ...
    ) ON join-clause
    

    Of course, each select there gets different sets of filters, different group by's, etc...

  • (disco)

    There's a typo: I'm pretty sure "C.CUST_SERVICE_REGI ON" in the last GROUP BY is supposed to be "C.CUST_SERVICE_REGION".

  • (disco) in reply to antiquarian

    In TDWTF system Database WTFs are represented by two separate, yet equally important groups: The programs that dump the entire DB processing everything in code, and the programs that attempt to do all the logic in SQL. These are their stories.

    BUMP BUMM!

  • (disco) in reply to g0uy0u
    g0uy0u:
    ANSI joins work fine when your query is structured like a simple tree, but when your join conditions are graph like, you end jumping all over the place to try to make sense of what is going on.

    And then you need to restructure one of the joins into an OUTER JOIN and the whole query goes to shit.

    Source: year(s) of experience. One too many...

  • (disco)

    It could be worse. The developer (who happens to live in India) could take your flowchart literally, and interpret the loops and branches usings LOOP and IF/THEN. So all these values were iterated over, additional queries run, branches branched, to make the whole thing excruciatingly slow. In one of the more complex things I've done in my career, I reduced the 1076 lines monstrosity to 440 lines, used a (VERY complex) query, and brought running time from around one hour to 16 seconds.

  • (disco) in reply to Severity_One
    Severity_One:
    The developer (who happens to live in India) could take your flowchart literally, and interpret the loops and branches usings LOOP and IF/THEN.

    Coding confession time:

    for firstRec in cFirst loop
    --...
      for secondRec in cSecond(firstRec.some_id) loop
      --...
        for thirdRec in cThird(secondRec.other_id) loop
        --...
    

    It also hits a temporary table in the meantime. In my defense, the queries in cursors are already a good 20-30 lines long, and joining them against each other is fairly impossible.

  • (disco)

    Based on my fifteen years of experience, I find ANSI joins way easier to read. Personal preference for "readable" is subjective, but I think a preference for ANSI style joins is the majority opinion. There are also issues with doing outer joins using the old-style syntax (if your DBMS even supports it, which some no longer do).

    Anyway, to actually help the original poster:

    (1) I suspect that instead of two big queries UNIONed together, this could be combined into one, which would be faster.

    (2) I think there is a bug. I suspect this:

      AND  ( 
                          item.country IS NOT NULL 
                 AND      c.country_cd = item.country 
                 OR       item.country IS NULL 
                 AND      c.country_cd = e.country) 
    

    Should actually be:

    AND ( 
                      (item.country IS NOT NULL AND c.country_cd = item.country)
             OR (item.country IS NULL AND c.country_cd = e.country)  )
    

    I mean, I can't be sure, by my intuition is that the latter is the intended logic. If this is stored server-side in a stored procedure or something, a couple of inline comments could be used to make it clear.

  • (disco)

    I get it! TRWTF is SQL itself, right?

  • (disco) in reply to beeporama
    beeporama:
    There are also issues with doing outer joins using the old-style syntax (if your DBMS even supports it, which some no longer do).

    Or never did -- I don't think SQLite3 ever supported any sort of old-style outer join syntax, for instance...

  • (disco) in reply to HardwareGeek
    HardwareGeek:
    GROUP BY n1.order_id, n1.n1_status_id, n1.sku_id, o.site_id, o.order_dt, ... ``` Obviously, I don't know their data, but wouldn't/shouldn't the `order_id` be unique, rendering the rest of the terms in the `GROUP BY` clause superfluous?

    Probably there is an entry for each status (e.g. received, checked, confirmed, sent, received, paid) the order runs through. That could be used to track the timestamps per order step. And probably several storage locations can be needed to fullfil an order. That would explain the addition of the "o" table.

  • (disco)

    Ah, good old optimizer_features_enable('9.2.0')...because why retest the application every five years there is a major new Oracle release? Let's throw away all improvements done on the optimizer in the last 13 years...god forbid it might even make sense out of this mess!

  • (disco) in reply to beeporama

    I suspect you are right about the bug -- and once again, ANSI join syntax would have made things clearer. I would want to see something like this... LEFT OUTER JOIN country itm_ctry ON item.country = itm_ctry.country_cd LEFT OUTER JOIN country e_ctry ON e.country = e_ctry.country_cd

    .

  • (disco) in reply to g0uy0u
    g0uy0u:
    ANSI JOIN: NO!They look nice and work for simple models but when you start making complex joins, or develop complex queries, you stop using them: it makes the code unreadable or unworkable. The key is properly naming your alias.

    Did you notice how they used a UNION to set a column? reissue_ind is set based on which part of the query you are in. Was this necessary or could you modify the WHERE clause and have a calculated column? Note that the problem isn't that they did that, it is that answering the question of whether it was the right thing to do is nearly impossible now.

    BTW for the curious the set of tables is different so this way avoid OUTER joins, but that isn't necessarily a good thing. It also accepts two sets of variables, using one for the former and another for the latter. This likely means they are duplicating the inputted parameters but there is no way to be sure.

    By using ANSI joins (and a couple OUTER joins) you would end up with half the code and could more easily tell what the relationships are.

    Also using ANSI joins does not preclude you from excluding things in the WHERE clause. For instance I usually try to do primary keys in the ON and anything more complex in the WHERE to avoid that issue to great effect.

  • (disco)

    For some reason it appears that this was a query that slowly aggregated over a period of time to become the query we see here. This is similar to the Winchester mystery house theory that one must keep building to stay alive. Unfortunately the only real solution is a tactical nuclear strike to eliminate the enemy and start over. I'm sure that a good BOFH would have a solution that involves "extreme prejudice" somewhere, and that might be necessary as well.

    I'm sure that this started out with "good intentions", but the path to hell is paved with them, and this is the end of the line.

  • (disco) in reply to herby
    herby:
    I'm sure that a good BOFH would have a solution that involves "extreme prejudice" somewhere

    No, a good BOFH would have a solution that involves a bag of lime and little-travelled forest path accessible by car.

  • (disco) in reply to HardwareGeek
    HardwareGeek:
    Why is the enterprise table used three times with different aliases?

    A quick and obvious example:

             prs.legal_name        AS parent_reseller_legal_name,```
    Those are presumably _different_ enterprise rows.
    
    <BLOCKQUOTE class="Quote"><div><i class="icon-quote"></i> <strong>HardwareGeek:</strong></div><div>
    Obviously, I don't know their data, but wouldn't/shouldn't the order_id be unique, rendering the rest of the terms in the GROUP BY clause superfluous?
    </div></BLOCKQUOTE>
    
    My guess is "orders_n1 is something like an order-line table with a many-to-one relationship with orders, and also different order_n1s can be in a different status for the same order".
    
    ETA:  blah blah :hanzo: blah.
    

Leave a comment on “Sea of SQL”

Log In or post as a guest

Replying to comment #:

« Return to Article