Re: Trouble using the ranking function in PL/SQL

Johnetta wrote:
>
> Hi,
>
> I am currently trying to use Oracle's Rank function in a PL/SQL cursor.
> When trying to compile the code snippet below, I receive a PLS-00103 Error.
>
> BEGIN
> FOR c_rec IN
> ( SELECT
> NVL(Category,'Dealers and Bankers') AS Category,
> NVL(currency_grp_loc,'ALL') AS Currency,
> broker_name,
> NVL(Prd_Grp_Name,'ALL') AS Product_grp,
> time_period,
> Sum_Amount_USD,
> Rank() over ( PARTITION BY Time_Period,
> Category,
> Prd_Grp_Name
> ORDER BY Time_Period,
> Category ) AS Rank,
> NULL AS Err_location,
> NULL AS Err_msg
> FROM dmIMD_Owner.IMDTrdCube_Tbl t
> WHERE broker_name IS NOT NULL
> AND prd_grp_name IS NOT NULL
> AND category IS NOT NULL
> AND currency_grp_loc ='ALL'
> ORDER BY nvl(time_period,to_date('01/01/1900','mm/dd/yyyy')),
> Category ,
> rpt_srt)
> LOOP
> << do stuff Here>>
>
> END LOOP;
>
> END;
>
> ORA-06550: line 10, column 21:
> PLS-00103: Encountered the symbol "(" when expecting one of the following:
>
> , from
>
> If I run the SQL statement for the cursor seperately, It runs perfectly.
>
> Any suggestions would be greatly appreciated.
>
> Thanks,
> Johnetta
>
> SQL>
PL/SQL lags behind a little - you will need to wrap the analytic code within EXECUTE IMMEDIATE. Rumoured to be resolved in 9i

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."