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 <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 <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.
