sfpug-oracle_sql_correlated_subqueries

This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.



To: sfpug@sf.pm.org
From: Joe Brenner <doom@kzsu.stanford.edu>
Subject: [sf-perl] [OT] SQL question
Date: Thu, 26 Jul 2001 15:45:56 -0700

Sorry about the not-very-perl topic here, but we were
talking about this a bit at the sfpug pizza meeting, so I
thought I would lay my SQL question on the list.

Let's say we want to define a bonus: One percent of the
gross is going to go straight into the pocket of whoever
made the sale.

So we might do something like this to get the bonus for each
member of the sales staff. 
     
   SELECT employee,  sum(amount) * 0.01  AS bonus
   FROM sales
   GROUP BY employee

For some reason or other, we want this stored in the 'emp'
table.  How do we do it?  Something like this is never going
to work, in Oracle, is it?

   UPDATE emp
   SET 
       bonus = (   SELECT employee AS current_employee,
       sum(amount) * 0.01  AS bonus
                   FROM sales
                   GROUP BY employee
               )
   WHERE emp.employee = current_employee


The subselect and the WHERE condition don't talk to each
other, and it's just going to say "Invalid column name" 
on the WHERE. 


My contention is that there is no way to do this in Oracle's
SQL, and that you need to use a scripting language in the
solution (preferably perl, maybe PL/SQL if one were
desperate for speed). 

===

To: sfpug@sf.pm.org, Joe Brenner <doom@kzsu.stanford.edu>
From: Jay Stanley <beansboy@yahoo.com>
Subject: Re: [sf-perl] [OT] SQL question
Date: Thu, 26 Jul 2001 17:08:39 -0700 (PDT)

Joe - I'm not sure if this'll make it to sfpug as I'm
posting from another place, so I'm sending this to you
directly as well.

What you need is a correlated subquery.  Try this:

   UPDATE emp e
   SET bonus = 
     (SELECT sum(amount) * 0.01
      from sales s
      where s.employee = e.employee);

Does that give you what you're expecting?

-jay stanley
-Oracle DBA
-http://www.dbspecialists.com/

=== 

To: "'sfpug@sf.pm.org'" <sfpug@sf.pm.org>
From: Erin Conel <Erin@planetU.com>
Subject: RE: [sf-perl] [OT] SQL question
Date: Thu, 26 Jul 2001 17:07:03 -0700

This *is* possible in Oracle. It's called a correlated subquery. If you have
access to Oracle Documentation it's covered in the SQL Reference stuff under
the UPDATE statement. If I understand your question correctly, the following
is the update statement you'll want to use. You get the 'Invalid Column
Name' error because their should be a join in the subquery to tie the sales
and emp tables together.  Also, you're columns in the update statement don't
match the subquery, so that would have given you an error as well. (There's
no 'employee' column referenced in your set clause, but it's selected in
your subquery.) As always, test it first.

UPDATE emp
   SET bonus = (SELECT sum(amount)*0.01 AS bonus
                  FROM sales
                 WHERE sales.employee = emp.employee 
                 GROUP BY sales.employee)
 WHERE emp.employee = current_employee
;



===

To: sfpug@sf.pm.org
From: Kirit Sarvaiya <kirits@yahoo.com>
Subject: Re: [sf-perl] [OT] SQL question
Date: Thu, 26 Jul 2001 17:34:54 -0700 (PDT)

You're right.  PL/SQL (faster) or Perl would have to
be employed.

Which is why....

SQL = Structured QUERY Language
PL/SQL = Programmable Language/Structured Query
Language

perl = Practical Extraction and Reporting Language
(amongst others)



===

To: sfpug@sf.pm.org
From: Kirit Sarvaiya <kirits@yahoo.com>
Subject: RE: [sf-perl] [OT] SQL question
Date: Thu, 26 Jul 2001 17:38:45 -0700 (PDT)

Erin,

In your query, what's current_employee?

Seems to me that your current_employee is defined
elsewhere
like: var current_employee = 1;

I believe Joe is asking about updating ALL employees
in the emp table.

Kirit.


===

To: sfpug@sf.pm.org
From: Joe Brenner <doom@kzsu.stanford.edu>
Subject: Jay Stanley: Re: [sf-perl] [OT] SQL question
Date: Thu, 26 Jul 2001 18:20:35 -0700

Yes, Erin is right, "correlated subqueries" are indeed what
I need to look up.  There's an example of their use here 
(you need to register to get an oracle "technet" account to 
see this):

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/state27a.htm#2067717

This is what I get for trying to rely on the O'Reilly book,
"Oracle SQL The Essential Reference".  This is definitely
not one of the good O'Reilly's...

Jay Stanley also pointed me in that direction, but his
message didn't make it to the list (and yes Kirit, I am 
trying to update all the employee records in one shot,
that's exactly what Jay's code does):


===

the rest of The Pile (a partial mailing list archive)

doom@kzsu.stanford.edu