SQL Confusion
Date: Fri, 3 Sep 2021 14:49:36 +0000
Message-ID: <0e5046489d764e749f3a149399adacbc_at_ex04test91a.ad.rit.edu>
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<mailto: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:49:36 CEST