Re: SQL Confusion

From: <l.flatz_at_bluewin.ch>
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-l
Received on Fri Sep 03 2021 - 16:57:37 CEST

Original text of this message