The FROM Clause
-
I should indeed have called in sick today:
Table and column names deleted to protect the innocent.
-
@blakeyrat said in The FROM Clause:
deleted to protect the innocent
The creator of this is anything but innocent.
-
@dkf The schema itself is perfectly sensible. I have no idea why the FROM clause was written this way when it could have been written in a sane way.
The problem is, I have to remove a column at the bottom of the pyramid, and it's all crumbling down around me. I told my boss, "hey this ticket's going to take twice as long because I have to rewrite this before I can make the change."
-
@blakeyrat The mythical 7th normal form in usage.
-
@cartman82 said in The FROM Clause:
@blakeyrat The mythical 7th normal form in usage.
Somewhere, Fabian Pascal has sprung a massive boner.
-
Another WTF is the date '6/1/2016' whose interpretation is dependent on the database settings. I'll bet my lunch money on the author not having set that explicitly.
-
@cark said in The FROM Clause:
Another WTF is the date '6/1/2016' whose interpretation is dependent on the database settings. I'll bet my lunch money on the author not having set that explicitly.
Not taking that bet.
-
@Khudzlin said in The FROM Clause:
Not taking that bet.
Given who is reporting things and the field I know he's working in, I'd guess it's in M/D/Y form. (Blakey's health insurance system employer really doesn't need to worry about working with non-US locales.)
-
@cark said in The FROM Clause:
Another WTF is the date '6/1/2016' whose int
I put that in so I could run it as a query in SSMS instead of having to start our entire codebase and navigate through like 37 pages to test the thing.
-
@blakeyrat said in The FROM Clause:
@cark said in The FROM Clause:
Another WTF is the date '6/1/2016' whose int
I put that in so I could run it as a query in SSMS instead of having to start our entire codebase and navigate through like 37 pages to test the thing.
SSMS accepts '20160601' or '20160106' as dates, so there's no need to use an ambiguous format. It's supposed to be a standard date pattern that works no matter your flavor of SQL, but I have run into an issue where Oracle SQL Developer won't accept it, which I suspect may be because of some sort of regionalization setting.
-
@djls45 And yet: I don't give a fuck. The dates are normally filled-in by ADO. They're only there so I can debug my new query to ensure the resultset matches the old.
Jesus you people are annoying.
"Hey that skyscraper is quite nice, but that scaffolding around it is kind of ugly and not entirely level"
"Well the scaffolding is going to come down in just a few day--"
"If you used those adjustable uprights you could make the scaffolding level and it's more technically correct and hello I am a DailyWTF forum poster where even after someone tells me it's a temporary measure they know is bullshit, I still tell them why they're wrong and stupid!"
Yes, thank you, I know it's wrong and I'm a stupid dumb ass moron retard for doing it that way. I still do not give a shit.
-
:puke:
-
@blakeyrat I'm sorry. I didn't mean to irritate you, and I never called you an idiot. I simply said there was no need to use an ambiguous format. I never said you couldn't. I even noted an exception I found (to add a little more hate on Oracle, the sub-forum for which is , as I recall [please correct me if I'm wrong]). :P
As to your OP, with the massive chained joins like that, does each level have a
select MASSIVE_LIST_OF_COLUMNS_EVEN_THOSE_NOT_IN_FOLLOWING_ON_CLAUSE_SO_THEY_CAN_BE_USED_IN_NEXT_OUTER_JOIN'S_ON_CLAUSE from ...
with a correspondingon SEPARATE_COMPARISON_FOR_EACH SHARED COLUMN
? Even as it is, with all the tables and columns removed, it is still a pretty confusing query. I would hate to have to wrap my mind around what exactly is going on within it.BTW, how long does it take to run?
-
@djls45 said in The FROM Clause:
As to your OP, with the massive chained joins like that, does each level have a select MASSIVE_LIST_OF_COLUMNS_EVEN_THOSE_NOT_IN_FOLLOWING_ON_CLAUSE_SO_THEY_CAN_BE_USED_IN_NEXT_OUTER_JOIN'S_ON_CLAUSE from ... with a corresponding on SEPARATE_COMPARISON_FOR_EACH SHARED COLUMN?
Huh?
No the SELECT clause is quite sensible. But I have no idea what you're asking because it's gibberish. There's also no WHERE clause at all-- the few conditionals are all in the FROM.
@djls45 said in The FROM Clause:
Even as it is, with all the tables and columns removed, it is still a pretty confusing query. I would hate to have to wrap my mind around what exactly is going on within it.
I have an Excel sheet half-completed with the "guide" as to which joins are inner and outer and which tables are joined to which other tables. When I finish the Excel sheet, re-writing the FROM clause in a sensible format will be easy.
@djls45 said in The FROM Clause:
BTW, how long does it take to run?
On the lab servers, it only takes a few seconds. I don't know how long it takes in production, as we're a HIPAA organization and I don't have access to production.
-
@djls45 said in The FROM Clause:
I simply said there was no need to use an ambiguous format.
He was just messing around in SSMS. And he used the way he normally typed dates for that. Is that really that bad?
-
@aliceif said in The FROM Clause:
Is that really that bad?
Apparently it makes me Hitler Satan. Like everything I do and talk about on this forum. Because I'm incapable of doing anything correct, ever.
-
@blakeyrat said in The FROM Clause:
But I have no idea what you're asking because it's gibberish.
I think he was trying to ask if the joins were actual tables or subqueries.
-
@blakeyrat said in The FROM Clause:
Because I'm incapable of doing anything correct, ever.
You should work on that.
-
When I've seen brackets around each join before, the query has always originated in Access. Never seen them nested pyramid style like that though
-
@boomzilla said in The FROM Clause:
@blakeyrat said in The FROM Clause:
Because I'm incapable of doing anything correct, ever.
You should work on that.
Bit of a catch22, eh?
-
@blakeyrat said in The FROM Clause:
Because I'm incapable of doing anything correct, ever.
Awww, the Haldol wore off again.
-
Finally got a couple hours to finish the rewrite. The new one runs a bit slower, but it also does a lot more logic in the WHERE clause and also it has to run a subquery because the column we removed actually had some behavior that I couldn't figure out how to replicate without one, so... whatever. Hopefully nobody complains.
It's a hell of a lot more readable, that's the important thing.
-
@blakeyrat said in The FROM Clause:
It's a hell of a lot more readable, that's the important thing.
Thank you for thinking of the future!
-
@blakeyrat I look forward to someone else making a thread on a forum somewhere to complain about a 2 minute long daily reporting process that had a 0.01s query that runs 10,000 times now taking 7+ hours to run a 3s query 10,000 times
-
@darkmatter I don't care about that person, because they don't work here.