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