Re: Multiplying columns from different rows

Hi,

If all the numbers are the same,
try a GROUP BY

sELECT
Cust_id Location MAX(perc_overflow) FROM ...
GROUP BY Cust_id Location

or, if the logic is more complicated, try writing a function that will go into the database and get the info you need.

create or replace function
my_func ( input_var1 IN varchar2 )
return number as

my_variable NUMBER := 0;
(
Select max(.. )
into my_variable
from other_table
where x = input_var1;

my_variable := my_variable * 2;
/* etc. */

RETURN MY_VARIABLE; )

You can create a cursor, cursor through it, and add, average, etc. things up.

Use it with:

Select Cust_id, Location ,
my_func (cust_id )
from ....

Functions are not the most efficient, but they do allow you to do procedural logic in the select. They can be extremely useful.

Argosy

In article <8504lv$agt$1_at_nnrp1.deja.com>,   milesr_at_my-deja.com wrote:
> Hello All,
>
> I'm trying to developed the code to write one cust_id per row,
with
> the perc_overflow column mulitlpied by other perc_overflow columns
with
> the same cust_id. I'm not sure whether I need a cursor, trigger,
control
> structure, etc...
>
> This is an abbreviated report format of the database:
>
> Cust_id Location perc_overflow Phone_ext Study_Hour
> 159 FT. Bragg 42 3145 03
> 159 FT. Bragg 5 3145 03
> 159 FT. Bragg 1 3145 03
> 160 FT. Myer 8 9934 10
> 160 FT. Myer 12 9934 10
> 161 FT. Meade 14 1253 12
> 161 FT. Meade 13 1253 12
> 161 FT. Meade 16 1253 12
>
> Now, if they have the same Cust_id, I need to multiply the
> perc_overflow columns so I'd only see one record per Cust_id and the
> perc_overflow column would represent the total percentage of over
flowed
> circuits for that Cust_id.
>
> The desired output would read for example:
>
> Cust_id Location perc_overflow Phone_ext Study_hour
> 159 FT. Bragg 210 3145 03
> 160 FT. Myer 96 9934 10
> 161 FT. Meade 2912 1253 12
>
> Could someone please give me some guidance towards accomplishing
this
> task in PL/SQL. I'm still not getting any output, probably because
none
> of my queries make any sense.
>
> Thanks,
> Ronald
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 05 2000 - 14:55:10 CST