Re: SQL max value

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

This is because the SQL parser of 8i's PL/SQL lags behind the 'standalone' SQL parser. So, in order to use the newest SQL features within PL/SQL you need to do that dynamically.

Something like this should get you started:

declare
  type rc is ref cursor;
  c rc;
  v_1 ...... variables to fetch into
  v_2 ......
begin
  open c for 'select ... from ...';

  loop
    fetch c into into v_1, v_2...;
    exit when c%notfound;

• you're processing of the variables v_...

  end loop;
  close c;
end;
/

hth
Rene Nyffenegger

-- 
  no sig today