RE: SQL Confusion
Date: Mon, 6 Sep 2021 20:02:18 +0000
Message-ID: <CO1PR19MB498454201FA9F104C273E9BD9BD29_at_CO1PR19MB4984.namprd19.prod.outlook.com>
What fellow dinosaur Mark said.
Perhaps the author might be interested in this The International Obfuscated C Code Contest (ioccc.org)<https://www.ioccc.org/>
(Although this isn't even CLOSE to the most obfuscated SQL I've had to decode).
And as long as we're telling war stories-
I once worked on a mortgage servicing system (and with the guy who wrote it) written ENTIRELY in IBM 360 Assembler. The company got acquired in a hostile takeover, and the programmer was asked to provide "a listing" of the code. He did so; but not before adding a set of macros at the front of the code that "translated" the entire character set to one that was not on the 1403 print chain. 25 BOXES of "greenbar" paper - the first 2 pages were the macros and then rest were blank except for the page numbers.
Clay
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Mark W. Farnham Sent: Sunday, September 5, 2021 10:03 AM To: jgebal_at_gmail.com; srcdco_at_rit.edu Cc: l.flatz_at_bluewin.ch; oracle-l_at_freelists.org Subject: RE: SQL Confusion
CAUTION: This email originated from outside of the organization. Do not follow guidance, click links, or open attachments unless you recognize the sender and know the content is safe.
nice.
by the way, as a general rule (just like putting in all the parentheses in an arithmetic expression) use an alias for exactly one object or projection in a query. The general rule being based on the axiom that the point of source code is clarity rather than a test of whether you remember the rules of either precedence of operators or scoping rules for names within queries.
An excellent thinking piece demonstrating this was once concocted by Brig Elliott (if memory serves) as part of the operating systems programming course for DCTS (the college retained piece of DTSS).
He wrote one expression as tersely as possible, another fully declined so that the meaning was obvious. After compiling and linking the object code using the PL/I compiler with all its passes of optimization and stripping the symbol table, the results were identical. Meaning the computer didn't favor the shorter source code AT ALL.
When asked what the code snippets meant, the principal author of the compiler (Phil DL Koch), easily read off the results of the first and remarked it would be quicker to run the compiler on the terse once than figuring it out. "I could probably do it, but why would I want to, and that would get no better than a D, presuming it does in fact deliver the correct results, which is not at all obvious."
Anyway, the dinosaur point is that clarity is king. Using the same alias for multiple different things is not clear.
All the best,
mwf
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jacek Gebal Sent: Sunday, September 05, 2021 2:18 AM To: srcdco_at_rit.edu<mailto:srcdco_at_rit.edu> Cc: l.flatz_at_bluewin.ch<mailto:l.flatz_at_bluewin.ch>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: Re: SQL Confusion
Sorry for bad colors:
insert into coopeval_owner.students_import_gradplusgradphdinsameterm
(select *
from coopeval_owner.students_import import
inner join (select *
from (select e.*
from coopeval_owner.students_import e
inner join (select term, rit_uid, lastname
from coopeval_owner.students_import
group by term, rit_uid, lastname
having count(rit_uid) > 1) dup
on dup.term = e.term
and dup.rit_uid = e.rit_uid) a
inner join (select e.*
from coopeval_owner.students_import e
inner join (select term, rit_uid, lastname
from coopeval_owner.students_import
group by term, rit_uid, lastname
having count(rit_uid) > 1) dup
on dup.term = e.term
and dup.rit_uid = e.rit_uid) b
on a.rit_uid = b.rit_uid
and a.term = b.term
and a.year <> 7
and b.year = 7) nongrad
on import.rit_uid = nongrad.rit_uid
and import.term = nongrad.term
and import.year = nongrad.year);
On Sun, 5 Sept 2021 at 09:16, Jacek Gębal <jgebal_at_gmail.com<mailto:jgebal_at_gmail.com>> wrote: The problem is the "SELECT *" in the A and B subquery. The * will create duplicate columns RIT_UID and TERM. Use select E.* if that is what you need and add other columns from DUP if needed.
Like this:
insert into coopeval_owner.students_import_gradplusgradphdinsameterm
(select *
from coopeval_owner.students_import import
inner join (select *
from (select e.*
from coopeval_owner.students_import e
inner join (select term, rit_uid, lastname
from coopeval_owner.students_import
group by term, rit_uid, lastname
having count(rit_uid) > 1) dup
on dup.term = e.term
and dup.rit_uid = e.rit_uid) a
inner join (select e.*
from coopeval_owner.students_import e
inner join (select term, rit_uid, lastname
from coopeval_owner.students_import
group by term, rit_uid, lastname
having count(rit_uid) > 1) dup
on dup.term = e.term
and dup.rit_uid = e.rit_uid) b
on a.rit_uid = b.rit_uid
and a.term = b.term
and a.year <> 7
and b.year = 7) nongrad
on import.rit_uid = nongrad.rit_uid
and import.term = nongrad.term
and import.year = nongrad.year);
On Fri, 3 Sept 2021 at 18:08, Scott Canaan <srcdco_at_rit.edu<mailto:srcdco_at_rit.edu>> wrote: It definitely doesn't like lateral with inner join.
Scott Canaan '88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco_at_rit.edu<mailto:srcdco_at_rit.edu> | c: (585) 339-8659
CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.
From: l.flatz_at_bluewin.ch<mailto:l.flatz_at_bluewin.ch> <l.flatz_at_bluewin.ch<mailto:l.flatz_at_bluewin.ch>> Sent: Friday, September 3, 2021 10:58 AM To: Scott Canaan <srcdco_at_rit.edu<mailto:srcdco_at_rit.edu>> Cc: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: Re: SQL Confusion
Hi,
I might be wrong: but the from clause is seen as concurrent. you could try Lateral.
https://www.geeksforgeeks.org/lateral-keyword-in-sql/<https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.geeksforgeeks.org%2Flateral-keyword-in-sql%2F&data=04%7C01%7Cclay.jackson%40quest.com%7C0cf7560d59c94a20fde708d9708f2b6b%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C637664582477015570%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=8WJjZXSZsXYoldR5SIjdNbX%2FFJPmESBWt9u3WnvoaXQ%3D&reserved=0>
----Ursprüngliche Nachricht----
Von : srcdco_at_rit.edu<mailto:srcdco_at_rit.edu>
Datum : 03/09/2021 - 16:49 (MS)
An : oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
Betreff : SQL Confusion
I am trying to convert a complicated SQL Server T-SQL procedure to PL/SQL. I am having trouble with one section in particular and can't figure out what they were doing and how to convert it.
The T-SQL code is:
DELETE ..students_import
OUTPUT DELETED.*
INTO Students_Import_GradPlusGradPHDInSameTerm
FROM ..students_import import
INNER JOIN (SELECT a.*
FROM (SELECT E.*
FROM ..students_import E
INNER JOIN (SELECT Term,
UID,
LastName
FROM ..students_import E
GROUP BY UID,
Term,
LastName
HAVING COUNT(UID) > 1
) dup
ON dup.Term = E.Term
AND dup.UID = E.UID
) a
INNER JOIN (SELECT E.*
FROM ..students_import E
INNER JOIN (SELECT Term,
UID,
LastName
FROM ..students_import E
GROUP BY UID,
Term,
LastName
HAVING COUNT(UID) > 1
) dup
ON dup.Term = E.Term
AND dup.UID = E.UID
) b
ON a.UID = b.UID
AND a.Term = b.Term
AND a.Year <> 7
AND b.Year = 7
) nongrad
ON import.UID = nongrad.UID
AND import.Term = nongrad.Term
AND import.Year = nongrad.YEAR
What I tried in PL/SQL is (followed by a separate delete statement): insert into coopeval_owner.students_import_gradplusgradphdinsameterm
(select *
from coopeval_owner.students_import import
inner join (select *
from (select *
from coopeval_owner.students_import e
inner join (select term, rit_uid, lastname
from coopeval_owner.students_import
group by term, rit_uid, lastname
having count(rit_uid) > 1) dup
on dup.term = e.term
and dup.rit_uid = e.rit_uid) a
inner join (select *
from coopeval_owner.students_import e
inner join (select term, rit_uid, lastname
from coopeval_owner.students_import
group by term, rit_uid, lastname
having count(rit_uid) > 1) dup
on dup.term = e.term
and dup.rit_uid = e.rit_uid) b
on a.rit_uid = b.rit_uid
and a.term = b.term
and a.year <> 7
and b.year = 7) nongrad
on import.rit_uid = nongrad.rit_uid
and import.term = nongrad.term
and import.year = nongrad.year);
The problem I'm having is that when I try to compile it, it complains that b.rit_uid and b.term don't exist at the lines in red. I'm not seeing why they aren't available at that point. It doesn't complain about a.rit_uid and a.term.
Scott Canaan '88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco_at_rit.edu<mailto:srcdco_at_rit.edu> | c: (585) 339-8659
CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Sep 06 2021 - 22:02:18 CEST