Re: sql select sum stored procedure

Hi Ken,
I think your (and mine, too) PL/SQL-Version can't manage this. Have you tried to create an inner view with the select-stmt ?

select rh.case_no into v_ct

    FROM w_wage_employer rwe, w_rg101 rh, w_wage_case rwc,

               ( SELECT SUM(gwsc.amt_payment),cd_source,...
                     FROM w_gws_client gwsc
                     WHERE   gwsc.cd_income            = '1'           AND
                                     gwsc.is_income            = 'I'
                     group by cd_source,...  ) gwsc
   where .../* your "old Where" */
           and         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.dt_claim             >= rwc.dt_month_1
AND
                           gwsc.dt_claim             <= rwc.dt_month_3
AND
                           gwsc.client_no             = rwe.client_no

"Ken Chesak" <datavector_at_hotmail.com> schrieb im Newsbeitrag news:3f2f39c4.0302110723.29dc61d3_at_posting.google.com...
> The error message is PLS-00103: Encountered the symbol "SELECT" when
expecting
> one of the following. This error is caused by the line
> rwe.amt_wages - ( SELECT SUM(gwsc.amt_payment)
>
> The sum is an amount which is calculated and then used as part of the
join.
>
>
> "Dirk Tschentscher" <dirkTHIS_at_REMvolkswagen.de> wrote in message
news:<b2aces$tj2_at_doiweb4.volkswagen.de>...
> > Hello
> >
> > What is your error ?
> >
> > Dirk
> >
> > "Ken Chesak" <datavector_at_hotmail.com> schrieb im Newsbeitrag
> > news:3f2f39c4.0302101432.1694076b_at_posting.google.com...
> > > 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;
Received on Wed Feb 12 2003 - 00:45:26 CST