Re: SQL max value

It wont work in your SP since your PL/SQL engine version is probably not up to speed with the 'over' function :-(

M

datavector_at_hotmail.com (Ken Chesak) wrote in message news:<3f2f39c4.0302271200.6debab66_at_posting.google.com>...
> That SQL works standalone, however it wont work in a stored procedure.
> Is the syntax valid? It is Oracle Enterprise 8.1.7.2.0.
>
> The compiler does not like the keyword 'over'.
>
> for r in
> (SELECT *
> FROM (SELECT rh.case_no, rwe.client_no, rwe.id_employer, rh.dt_run,
> row_number()
> over (partition by rh.case_no, rwe.client_no, rwe.id_employer order
> by rh.dt_run desc) rn
> FROM wage_employer rwe, rg101 rh, rg101_detail rhd,
> w_rg101 wrh, w_wage_employer wrwe, w_rg101_detail wrhd
> WHERE rwe.id_rg101 = rh.id_rg101 AND
> rh.case_no = wrh.case_no AND
> rwe.client_no = wrwe.client_no AND
> rwe.id_employer = wrwe.id_employer AND
> wrwe.id_rg101 = wrh.id_rg101 AND
> rhd.cd_suppress <> 'NONE')
>
> where rn = 1)
> loop
> update w_rg101_detail
> set
> cd_disposition = 'NOSU',
> dt_updated = sysdate,
> id_rule = '6.1.1';
> --cd_suppress = r.cd_suppress
> -- where id_rg101_detail = r.id_rg101_detail;
>
> v_rowcount := v_rowcount + SQL%ROWCOUNT;
>
> end loop;
Received on Fri Feb 28 2003 - 02:51:59 CST