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

Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL question

Re: PL/SQL question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 08 Aug 1998 13:55:04 GMT
Message-ID: <35cc5733.1285638@192.86.155.100>


A copy of this was sent to iancrozier_at_aol.com (Iancrozier) (if that email address didn't require changing) On 7 Aug 1998 21:58:34 GMT, you wrote:

>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.
>

I don't know what 'the ratio of the sum of all C's for a particular value' means. Guessing at what it means you could try:

select t1.a, t1.avg_b, t2.sum_c
from ( select A, avg(B) avg_b from T group by A ) t1,

     ( select A, sum(C) sum_c from T where D = 1 group by A ) t1 where t1.a = t2.a (+)

or even:

select A, avg(B) avg_b, sum( decode( D, 1, C, 0 ) ) sum_c   from T
 group by A
/

they will give you the same answer... Perhaps you can elaborate on what result set you are trying to get with an example of table data and required output. You can probably do it in one query with an inline view or by using decode.

>Questions:
>
>1. Is is possible to create an array in PL/SQL e.g.
>
>v_sum_C(n) number(6,3);
>

Yes,

declare

    type myArray is table of number index by binary_integer;     v_sum_C myArray;
begin

    ....
end;

>(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.
>
>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?
>

No, no temporary tables. You can put it into a pl/sql table as above in the following manner:

   for x in ( select sum(c) sum_c from table where d = 1 group by a )    loop

       v_sum_c( v_sum_c.count+1 ) := x.sum_c;    end loop;

>Something like:
>select sum(C) from table where D = 1 group by A into table2.sumc
>
>3. If I can't do either 1 or 2, how can I do this?
>
>Thanks in advance
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Aug 08 1998 - 08:55:04 CDT

Original text of this message

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