Re: SQL Confusion
Date: Fri, 3 Sep 2021 16:57:37 +0200 (CEST)
Message-ID: <1057865180.28243.1630681057827_at_bluewin.ch>
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
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_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 Fri Sep 03 2021 - 16:57:37 CEST