RE: SQL Confusion
Date: Sun, 5 Sep 2021 13:03:20 -0400
Message-ID: <126901d7a277$ee558930$cb009b90$_at_rsiz.com>
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] On Behalf Of Jacek Gebal
Sent: Sunday, September 05, 2021 2:18 AM
To: srcdco_at_rit.edu
Cc: l.flatz_at_bluewin.ch; 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> 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> 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 | 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 <l.flatz_at_bluewin.ch>
Sent: Friday, September 3, 2021 10:58 AM
To: Scott Canaan <srcdco_at_rit.edu>
Cc: 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/
----Ursprüngliche Nachricht----
Von : srcdco_at_rit.edu
Datum : 03/09/2021 - 16:49 (MS)
An : 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 | 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 Sun Sep 05 2021 - 19:03:20 CEST