Re: sql select sum stored procedure

Karsten Farrell <kfarrell_at_belgariad.com> wrote in message news:<MPG.18b2e26b4ac7916f989699_at_news.la.sbcglobal.net>...
> datavector_at_hotmail.com said...
> > The following sql works stand alone in SQLPlus but wont work in a
> > stored procedure. The procedure fails at the line, gwsc.gws_seq =
> > (SELECT MAX(gws_seq). How can I make this work in a proc. Oracle
> > version 8.1.7.
> >
> > The error message is PLS-00103:Encountered the symbol "SELECT"..
> >
> >
> > select rh.case_no into v_ct
> > FROM w_wage_employer rwe, w_rg101 rh, w_wage_case rwc
> > WHERE rwe.cd_disposition = 'NONE' AND
> > rh.cd_case_category IN ('06','08','09') AND
> > rh.case_status = '2' AND
> > rwc.id_rg101 = rh.id_rg101 AND
> > rwc.id_wage = rwe.id_wage AND
> > rwe.amt_wages - (SELECT SUM(gwsc.amt_payment)
>
> (snip rest of sql statement)
>
> I don't know if this is the problem, but the pl/sql engine hasn't always
> kept up with the features available in sqlplus. Since inline views are
> fairly recent (relative to oracle history), I suspect this particular
> version of inline view might be one of those cases where the pl/sql
> engine lags behind. Make a simple, non-functional stored procedure and
> see if it compiles on your database ... something like (only did an
> eyeball syntax check on this):
>
> create or replace procedure xyz is
> v_ct number;
> begin
> for x in (
> select rh.case_no into v_ct
> from w_wage_employer rwe, w_rg101 rh
> where (rwe.amt_wages - (select count(*) from dual)) > 10
> )
> loop
> null;
> end loop;
> end xyz;
>
> Do NOT run it; just see if it compiles. That way, you don't have to deal
> with a very long sql just to find out that it wouldn't compile even if
> it were simpler.
>
> If it doesn't compile, you might be able to "trick" the pl/sql engine by
> wrapping your sql in an execute immediate (which I believe bypasses the
> pl/sql engine; but not sure about this).

By changing the order I was able to compile the stored procedure. This also works for my original SQL. Thanks for your help.

Your example changed to:

create or replace procedure xyz is
  v_ct number;
begin
  for x in (
    select rh.case_no
    from w_wage_employer rwe, w_rg101 rh     where 10 <= (select rwe.amt_wages - count(*) from dual)     )
  loop
    null;
  end loop;
end xyz; Received on Tue Feb 11 2003 - 17:18:54 CST