Re: SQL Confusion
Date: Sun, 5 Sep 2021 09:18:15 +0300
Message-ID: <CACQ9E3suci-6oiStY8CtBqY9xMfhmDEsMUvz_LRxc7KXnOXOGA_at_mail.gmail.com>
Sorry for bad colors:
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 Sun, 5 Sept 2021 at 09:16, Jacek Gębal <jgebal_at_gmail.com> wrote:
> 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_at_rit.edu> 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_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-lReceived on Sun Sep 05 2021 - 08:18:15 CEST