Percentile function

[mghafoori]

Hello:
I need to find the 30th, 40th, 50th, ... percentile for a set of values. I know oracle has a percent_rank function, but it doesn't work the way i want it to. Therefore i wrot my own function to calculate that and in that function I need to get all the values and insert them into a temp table and ordered by dollar amount in ascending order. I want to use the following SQL statement in that function but I am getting an error:

select l.trend_charge, row_number()
over (order by trend_charge) as cume
from cms.lab_main l
where l.hcpcs_cd = '82030'
and l.trim_code is null

this sql statement runs perfectly in SQL, but when you have it in a PL/SQL code, then it is giving me an error saying that "from" is expected after the word "over". This SQL statement uses an Analytical function of oracle (row_number) which basically orders the list and assigns a rownumber as a value.

Can anyone think of why this statement runs in SQL but not in PL/SQL. Or can someone suggest another way of computing the "Percentile" for a list of values.

Thanks for your help.

Mason

 

[karluk]

Unfortunately, analytic functions are not supported in PL/SQL. That shortcoming is fixed in Oracle 9i. You must be on 8i, so you have to use a work-around or avoid PL/SQL completely.

I've heard of two work-arounds that should work. You can define a view using your analytic function and reference the view from inside PL/SQL. The other alternative is to use dynamic sql. You can put your query into a string variable and do an "execute immediate" on the string.

 

[mghafoori]

I had tried dynamic sql method earlier, but i was getting some kind of error. I tried it again and now it works. Thanks Karluk for your input.