Re: sql select sum stored procedure

Ken Chesak wrote:

> 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)
> FROM w_gws_client gwsc, w_wage_employer rwe, w_wage_case rwc
> 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 AND
> substr(rwe.employer_name,1,4) =
> substr(gwsc.employer_name,1,4) AND
> gwsc.gws_seq = (SELECT MAX(gws_seq)
> FROM w_gws_client gwsc0
> WHERE gwsc0.id_gws_client = gwsc.id_gws_client
> AND
> gwsc0.client_no = gwsc.client_no
> AND
> gwsc0.employer_name = gwsc.employer_name AND
> EXISTS (SELECT 1
> FROM w_gws_client gwsc1
> WHERE gwsc1.id_gws_client =
> gwsc0.id_gws_client AND
> gwsc1.gws_seq =
> gwsc0.gws_seq AND
> gwsc1.client_no =
> gwsc0.client_no AND
> gwsc1.employer_name =
> gwsc0.employer_name AND
> gwsc1.dt_claim =
> rwc.dt_month_1) AND
> EXISTS (SELECT 1
> FROM w_gws_client gwsc2
> WHERE gwsc2.id_gws_client =
> gwsc0.id_gws_client AND
> gwsc2.gws_seq =
> gwsc0.gws_seq AND
> gwsc2.client_no =
> gwsc0.client_no AND
> gwsc2.employer_name =
> gwsc0.employer_name AND
> gwsc2.dt_claim =
> rwc.dt_month_2) AND
> EXISTS (SELECT 1
> FROM w_gws_client gwsc3
> WHERE gwsc3.id_gws_client = gwsc0.id_gws_client
> AND
> gwsc3.gws_seq =
> gwsc0.gws_seq AND
> gwsc3.client_no =
> gwsc0.client_no AND
> gwsc3.employer_name =
> gwsc0.employer_name AND
> gwsc3.dt_claim =
> rwc.dt_month_3))) >= discrepancy2;

If I am understanding your exmaple ... In SQL*Plus you must select into a variable. You can not do an assignment. So, for example:

SET SERVEROUTPUT ON DECLARE
   x PLS_INTEGER;
BEGIN
   SELECT COUNT(*)
   INTO x
   FROM dual;
   DBMS_OUTPUT.PUT_LINE(x);
END;
/

works whereas

BEGIN
   x := SELECT ....

will not.

Daniel Morgan Received on Tue Feb 11 2003 - 14:20:44 CST