Re: Use of an array NOT number cause error

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... Received on Fri Jul 06 2007 - 09:08:44 CDT