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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Jacek Gebal
Sent: Sunday, September 05, 2021 2:18 AM
To: srcdco@xxxxxxx
Cc: l.flatz@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx> 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@xxxxxxx> 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@xxxxxxx | 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@xxxxxxxxxx <l.flatz@xxxxxxxxxx>
Sent: Friday, September 3, 2021 10:58 AM
To: Scott Canaan <srcdco@xxxxxxx>
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxx
Datum : 03/09/2021 - 16:49 (MS)
An : oracle-l@xxxxxxxxxxxxx
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@xxxxxxx | 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.
