Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Programming in SQL?

Re: Programming in SQL?

From: Arati Vijay <vijayj_at_singnet.com.sg>
Date: Wed, 27 Oct 1999 22:29:34 +0800
Message-ID: <7v72fo$vr8$1@coco.singnet.com.sg>


I presume you are summing on related keys. Try this

UPDATE TABLE A
SET AMOUNT = SELECT AMT FROM

                                 SELECT KEY_VALUES, SUM(AMOUNTS) AMT
                                 FROM TABLE B
                                 WHERE A.KEY_VALUES = B.KEY_VALUES)

You will have to tweak the above to get what you want. The idea is to use the result set returned by a SELECT like a table, instead of creating a view or using PL/SQL. This works on Oracle 8i.

<amerar_at_ci.chi.il.us> wrote in message news:7v4onm$bd7$1_at_nnrp1.deja.com...
>
>
> 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
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Oct 27 1999 - 09:29:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US