Re: sql select sum stored procedure

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 Mon Feb 10 2003 - 17:33:26 CST