Re: sql select sum stored procedure

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).

-- 
/Karsten