| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Use of an array NOT number cause error
On 6 jul, 16:08, Mariano <mariano.calan..._at_gmail.com> wrote:
> On 6 Lug, 15:53, DA Morgan <damor..._at_psoug.org> wrote:
>
>
>
> > Mariano wrote:
> > > Then I've this function:
>
> > > ======================================
>
> > > FUNCTION "SEMIAUTO"(px NUMBER)
>
> > > RETURN automatiche AS
>
> > > results automatiche := automatiche();
>
> > > CURSOR c_class IS
> > > SELECT id_mal, SUM(percent) AS p
> > > FROM malattie_sintomi
> > > WHERE id_sin IN (px)
> > > and id_mal in (select id_mal from malattie_sintomi group by
> > > id_mal having count(*) >= 1)
> > > GROUP BY id_mal;
>
> > > BEGIN
> > > FOR v_Rec IN c_class LOOP
> > > results.EXTEND;
> > > results(results.LAST) := automatica(v_Rec.id_mal,
> > > v_Rec.p);
> > > END LOOP;
> > > RETURN results;
> > > END;
>
> > > ======================================
>
> > > As you could see, this function accept a NUMBER in input, this number
> > > will be a parameter of my cursor c_class.
> > > But number it's not usefull for my job, I need to pass an unknow
> > > number of NUMBER (sorry for words joke :D) a sort of array.
>
> > > I've write:
>
> > > type sinArray is table of number;
>
> > > and now header of my function is something like
>
> > > FUNCTION "SEMIAUTO"(px SINARRAY)
>
> > > But in this way i have a conflict and a message advise me that is
> > > needed NUMBER not SINARRAY.
>
> > > What I can do?
>
> > Is your code supposed to do something useful?
>
> > If so ... please post the version number and the business problem you
> > are trying to solve. From the above function the only thing I can deduce
> > is that you trying to do something that will not work. But not knowing
> > what you are actually trying to do makes it hard to suggest a better
> > direction to go.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damor..._at_x.washington.edu (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org
>
> Version number: 10g
> Problem: I've a table malattie_sintomi, with 3 field id_sin, id_mal
> and percent (all numeric field).
> Data in table are stored as something like that:
> ID_MAL ID_SIN PERCENT
> 1 1 30
> 1 2 50
> 1 3 20
> 2 1 25
> 2 2 75
> 3 1 100
>
> Using (X will be a range of number, Y will be count of how many
> element are in the range X):
> SELECT id_mal, SUM(percent) AS p
> FROM malattie_sintomi
> WHERE id_sin IN ( X )
> and id_mal in (select id_mal from malattie_sintomi group by
> id_mal having count(*) >= Y)
> GROUP BY id_mal;
>
> Example Output:
> X=1 AND Y=1
> ID_MAL PERCENT
> 1 30
> 2 25
> 3 100
>
> again
> X=1, 2 AND Y=2
> ID_MAL PERCENT
> 1 80
> 2 100
>
> Hope that i've helped u in helping me...
SQL> create table malattie_sintomi
2 as
3 select 1 id_mal, 1 id_sin, 30 percent from dual union all
4 select 1, 2, 50 from dual union all 5 select 1, 3, 20 from dual union all 6 select 2, 1, 25 from dual union all 7 select 2, 2, 75 from dual union all 8 select 3, 1, 100 from dual
Tabel is aangemaakt.
SQL> var X varchar2(10) SQL> var Y number SQL> exec :X := '1'; :Y := 1
PL/SQL-procedure is geslaagd.
SQL> with x as
2 ( select to_number(x) x
3 from dual
4 model
5 return updated rows
6 dimension by (0 i)
7 measures (',' || :X || ',' x)
8 rules iterate (100) until (iteration_number =
length(x[0])-length(replace(x[0],','))-2)
9 ( x[iteration_number+1]
10 = substr
11 ( x[0]
12 , instr(x[0],',',1,iteration_number+1) + 1
13 , instr(x[0],',',1,iteration_number+2) -
instr(x[0],',',1,iteration_number+1) - 1
14 )
15 )
20 , count(*) over (partition by id_mal) count_mal 21 from malattie_sintomi m 22 ) 23 where id_sin in (select x from x) 24 and count_mal >= :Y
ID_MAL
1 30 2 25 3 100
3 rijen zijn geselecteerd.
SQL> exec :X := '1,2'; :Y := 2
PL/SQL-procedure is geslaagd.
SQL> with x as
2 ( select to_number(x) x
3 from dual
4 model
5 return updated rows
6 dimension by (0 i)
7 measures (',' || :X || ',' x)
8 rules iterate (100) until (iteration_number =
length(x[0])-length(replace(x[0],','))-2)
9 ( x[iteration_number+1]
10 = substr
11 ( x[0]
12 , instr(x[0],',',1,iteration_number+1) + 1
13 , instr(x[0],',',1,iteration_number+2) -
instr(x[0],',',1,iteration_number+1) - 1
14 )
15 )
20 , count(*) over (partition by id_mal) count_mal 21 from malattie_sintomi m 22 ) 23 where id_sin in (select x from x) 24 and count_mal >= :Y
ID_MAL
1 80 2 100
2 rijen zijn geselecteerd.
Regards,
Rob.
Received on Fri Jul 06 2007 - 10:35:32 CDT
 |
 |