Re: SQL Confusion

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

                                                                 group

                                                     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

> 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_at_rit.edu <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.
>
>
>
> *From:* l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch>
> *Sent:* Friday, September 3, 2021 10:58 AM
> *To:* Scott Canaan <srcdco_at_rit.edu>
> *Cc:* oracle-l_at_freelists.org
> *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_at_rit.edu
> Datum : 03/09/2021 - 16:49 (MS)
> An : oracle-l_at_freelists.org
> 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_at_rit.edu <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