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
p.MsoNormal, li.MsoNormal, div.MsoNormal {
margin: 0.0in;
margin-bottom: 1.0E-4pt;
font-size: 11.0pt;
font-family: Calibri , sans-serif;
}
a:link, span.MsoHyperlink {
mso-style-priority: 99;
color: rgb(5,99,193);
text-decoration: underline;
}
a:visited, span.MsoHyperlinkFollowed {
mso-style-priority: 99;
color: rgb(149,79,114);
text-decoration: underline;
}
span.EmailStyle17 {
mso-style-type: personal-compose;
font-family: Calibri , sans-serif;
color: windowtext;
}
*.MsoChpDefault {
mso-style-type: export-only;
font-family: Calibri , sans-serif;
}
div.WordSection1 {
page: WordSection1;
}
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.
