Re: SQL Confusion

  • From: Jacek Gębal <jgebal@xxxxxxxxx>
  • To: srcdco@xxxxxxx
  • Date: Sun, 5 Sep 2021 09:16:43 +0300

The problem is the "SELECT *" in the A and B subquery.
The * will create duplicate columns RIT_UID and TERM.
Use select E.* if that is what you need and add other columns from DUP if
needed.

Like this:

insert into coopeval_owner.students_import_gradplusgradphdinsameterm
(select *
from coopeval_owner.students_import import
inner join (select *
                                    from (select e.*
                                            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 e.*
                                                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);


On Fri, 3 Sept 2021 at 18:08, Scott Canaan <srcdco@xxxxxxx> wrote:

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 <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
Datum : 03/09/2021 - 16:49 (MS)
An : 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 <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: