Re: sql select sum stored procedure

Hi Daniel,
He has an into-clause : > select rh.case_no into v_ct

Rgds

    Dirk

"DA Morgan" <damorgan_at_exesolutions.com> schrieb im Newsbeitrag news:3E4836C6.44F7D5ED_at_exesolutions.com...
> Ken Chesak wrote:
>
> > Oracle 8.1.7
> >
> > This SQL works fine in SQLPlus but does not work in a stored
> > procedure.
> > In a stored procedure it does not like the (SELECT SUM( .
> >
> > 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)
> > FROM w_gws_client gwsc
> > WHERE gwsc.cd_source = rwe.cd_source
> > AND
> > gwsc.case_no = rwe.case_no
> > AND
> > gwsc.dt_run = rwe.dt_run
> > AND
> > gwsc.log_no = rwe.log_no
> > AND
> > gwsc.cd_income = '1'
> > AND
> > gwsc.is_income = 'I'
AND
> > gwsc.dt_claim >= rwc.dt_month_1
> > AND
> > gwsc.dt_claim <= rwc.dt_month_3
> > AND
> > gwsc.client_no = rwe.client_no)
> > >= discrepancy2;
>
> Of course not. The following SQL won't work in a stored procedure either:
>
> BEGIN
> SELECT SYSDATE FROM dual;
> END;
> /
>
> The reason is that in stored procedures, functions, packages, and triggers
you are not
> sitting there in SQL*Plus staring at the monitor. Therefore ... you must
select into a
> variable. For example:
>
> SET SERVEROUTPUT ON
>
> DECLARE
> d DATE;
> BEGIN
> SELECT SYSDATE
> INTO d
> FROM dual;
> DBMS_OUTPUT.PUT_LINE(d);
> END;
> /
>
> Daniel Morgan
>
Received on Tue Feb 11 2003 - 02:30:28 CST