Re: SQL max value

Rene

A simple method of doing this is to create a view and than select from the view in the package/procedure.

Regards

Graham

"Rene Nyffenegger" <rene.nyffenegger_at_gmx.ch> wrote in message news:b3lru7$1m5qog$1_at_ID-82536.news.dfncis.de...
>
> > 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
>
>
Received on Thu Feb 27 2003 - 16:31:11 CST