Re: Programming in SQL?

In article <7v4oqi$bf5$1_at_nnrp1.deja.com>,   amerar_at_ci.chi.il.us wrote:
>
>
> Hello,
>
> I am trying to create a very interesting query and I'm not sure if it
> can be done.
>
> I have two tables: Table A and Table B. There is a one to many
> relationship from Table A to Table B. What I want to do, for the
dollar
> amount fields, is for matching records in the tables, I want sum the
> records in Table B first, then add it to the field in Table A.
>
> So,
>
> Table A has one record with a dollar amount of $100.
> Table B has three records with dollar amounts $100, $100 and -$100.
>
> I want to sum those records in Table B BEFORE adding them to Table A.
>
> Can this be done or am I stuck writing a PL/SQL procedure?
>
> Please send a copy to my e-mail.
>
> Thanks,
>
> Arthur
> amerar_at_ci.chi.il.us

 An in-line view will work fine:

• (note, this is untested) ------

select a.ID, a.amount + subtotal
  from a,
  (select ID bid, sum(amount) subtotal from b group by ID) bsub  where a.ID = bid;

--
Ed Prochak
Magic Interface, Ltd.
ORACLE Development, conversions, training and support 440-498-3700 magic_at_interfacefamily.com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 26 1999 - 14:09:09 CDT