Re: PL/SQL question

From: Corinna Becker <Corinna.Becker_at_memo.ikea.com>
Date: 10 Aug 1998 06:50:13 GMT
Message-ID: <01bdc42b$1e062940$a049100a@pc743-sode.seurope.ikea.com>

Hello Ian,
I know that you can query a table twice in one select statement by giving the table a symbolic name.

select "ratio of SUM(t1.C) to SUM(t2.C)"                (Sorry, I don't
know the exact syntax here)
from your_table     t1,
        your_table    t2

where t1.A = 'any_value'
 and t2.D = '1';

(I'm not sure if this gives you the correct content. But this example shows you how to use a table twice in one select statement.)

To your questions below look at the ### text ###: I hope some of this helps you.
Best regards
Corinna

Iancrozier <iancrozier_at_aol.com> wrote in article <1998080721583400.RAA15046_at_ladder03.news.aol.com>...
> I have a table which includes four columns: A,B,C and D
> I need to create a report which will give me the average value of
> B for every unique value of A. That's easy.
> I also need to put in the report the ratio of the sum of all C's for a
> particular value
> of A to the the sum of all the values of C when D is equal to 1.
> I don't see any way to do it in SQLPLUS because we need to query the same
table
> in two different ways. So, it looks like I need to use PL/SQL.
>
> Questions:
>
> 1. Is is possible to create an array in PL/SQL e.g.
>
> v_sum_C(n) number(6,3);
> ### At least I couldn't find anything about arrays in the handbook. I
don't think it is possible. ###

> (where n can range from 1 to whatever I need)
> so I can feed different values of "select sum(C) from table group by A"
into
> one "variable array" instead of declaring a multitude of different
variables.
> Then, can I
> put those values into a temporary table where I can then do the necessary
> calculation for the report.

### In PL/SQL you could write something like:

select sum(C)
into v_sum1
from your_table
where A='any_value';

select sum(C)
into v_sum2
from your_table
where D='1';

v_Ratio = v_sum1 / v_sum2;

Then v_Ratio contains your result. ###

>
> 2. Can I feed the results of e.g.
>
> select sum(C) from table where D = 1 group by A
>
> directly into a temporary table?
>
> Something like:
> select sum(C) from table where D = 1 group by A into table2.sumc
>

### You can write: insert into tmp_table select anything from your_table; This is possible when both tables have the same columns. Or: insert into tmp_table (column_name) select sum(C) ..... That means you select exactly the columns that tmp_table consists of. Or finally with the PL/SQL code above:
insert v_Ratio into tmp_table;
###

> 3. If I can't do either 1 or 2, how can I do this?
>
> Thanks in advance
>
Received on Mon Aug 10 1998 - 01:50:13 CDT