Oracle. Pass result to custom function

Question

I have a custom function which is supposed to calculate upper and lower quartiles. I want to compute it in a following way:

SELECT QUARTILE_UTILS.GET_QUARTILE(0.25, YEAR_1_FTE),  
       QUARTILE_UTILS.GET_QUARTILE(0.75, YEAR_1_FTE)
FROM SOME_TABLE WHERE FIRM_ID=999;

So it looks like an aggregate function. It receives a number to calculate a quartule(.25 or .75) and a collection of numbers. So here is the function body:

create or replace PACKAGE BODY QUARTILE_UTILS AS
  FUNCTION GET_QUARTILE(PERCENTILE NUMBER, DATASET NUMBERS_ARRAY) RETURN NUMBER
  IS
    REMINDER NUMBER := 0;
    I SIMPLE_INTEGER := 0;
    RESULT NUMBER := 0;
  BEGIN
    ....
    RETURN RESULT;
  END;
END QUARTILE_UTILS;

And as you can see, I defined a custom data type. Here it is:

create or replace TYPE NUMBERS_ARRAY AS TABLE OF NUMBER;

However, it still doens't work. When I try to execute I receive such error:

PLS-306: wrong number or types of arguments in call to 'GET_QUARTILE'

As far, as I understand my definition of collection of number is incorrect. What I am doing wrong?

What data type is YEAR_1_FTE? Presumably not a nested table type. You want to aggregate over all the values from the table - I think - so are you trying to write a user-defined aggregate function? Or do you want to turn the values from the table into a collection and pass that to your current function? — Alex Poole
– Alex Poole, Commented Jun 17, 2016 at 12:14
@AlexPoole YEAR_1_FTE is just a number. So yes, I just need to pass a collection of numbers. — Petr Shypila
– Petr Shypila, Commented Jun 17, 2016 at 12:15

Alex Poole · Accepted Answer · 2016-06-17 12:21:06Z

1

Your function is expecting a collection, not a simple number; so you can use the COLLECT() function and cast what that produces to the expected table type:

SELECT QUARTILE_UTILS.GET_QUARTILE(0.25, CAST(COLLECT(YEAR_1_FTE) AS NUMBERS_ARRAY)),
       QUARTILE_UTILS.GET_QUARTILE(0.75, CAST(COLLECT(YEAR_1_FTE) AS NUMBERS_ARRAY))
FROM SOME_TABLE WHERE FIRM_ID=999;

You might want to investigate user-defined aggregate functions as an alternative though.

answered Jun 17, 2016 at 12:21

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Petr Shypila Over a year ago

Thank you for your answer. I saw this article about user-defined aggregate functions. I saw, that I should develop custom data type. For me it's just looks wierd that I should perform all these steps just to pass an array of integers.

Alex Poole Over a year ago

You have to create an array of integers from the individual numbers in each row. That doesn't seem unreasonable.

Arkadiusz Łukasiewicz · Accepted Answer · 2016-06-17 13:21:44Z

I know that my answer is offtopic . But maybe it helps you.

with abc as (select 1 lp from dual
                union all
                select 2 from dual
                union all
                select 4 from dual
                union all
                select 5 from dual
                union all
                select 8 from dual
                union all
                select 9 from dual
                union all
                select 10 from dual
                union all
             select 12 from dual)
select median(case when quartiles =1 then lp else null end), /* .25 */
       median(case when quartiles =3 then lp else null end) /* .75 */
from
( 
select 
       lp, ntile(4) over(order by lp) quartiles
from abc
)

Oracle also has PERCENTILE_DISC,PERCENTILE_CONT analytic/aggregation function.

Collectives™ on Stack Overflow

Oracle. Pass result to custom function

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related