| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> sql select sum stored procedure
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;
Received on Tue Feb 11 2003 - 11:18:39 CST
 |
 |