RE: SQL Confusion

  • From: Scott Canaan <srcdco@xxxxxxx>
  • To: "l.flatz@xxxxxxxxxx" <l.flatz@xxxxxxxxxx>
  • Date: Fri, 3 Sep 2021 15:08:00 +0000

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<mailto: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<mailto:srcdco@xxxxxxx>
Datum : 03/09/2021 - 16:49 (MS)
An : oracle-l@xxxxxxxxxxxxx<mailto: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<mailto: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.



Other related posts: