Re: SQL Statement Execution: HEELP!

From: Dwayne Remekie <dremekie_at_home.com>
Date: 2000/04/25
Message-ID: <39060b50_2@news1.prserv.net>

This is the procedure that I am testing.

create or replace procedure Test(mCrap int)  is
begin

    update comptocomp ctoc
    set (volume) =
    (

      select TargetCost/TotalTargetCost as volume from
      (
          select UpperSeqMapping.SourceCompID, UpperSeqMapping.TargetCompID,

UpperSeqMapping.SourceCost, decode(UpperSeqMapping.TargetCost, null, 0, UpperSeqMapping.TargetCost) as TargetCost, TotalTargetCostTable.TotalTargetCost

          from
          (
          select  decode(SourceCCost.Cost, null, 0, SourceCCost.Cost) as

SourceCost, Mapping.SourceCompID, Mapping.TargetCompID as TargetCompID, TargetCCost.Cost as TargetCost

          from
          ComponentCost SourceCCost,
          ComponentCost TargetCCost,
          CompToComp Mapping
          where Mapping.SourceCompID in (select compid from component where
sequence = 2 and compid >=5000000 and compid <=5099999 and SequenceType='C')
          and Mapping.PeriodID = 200001
          and Mapping.VersionID = 1088
          and SourceCCost.CompID (+)= Mapping.SourceCompID
          and SourceCCost.PeriodID (+)= 200001
          and SourceCCost.VersionID (+)= 1088
          and TargetCCost.CompID(+) = Mapping.TargetCompID
          and TargetCCost.PeriodID (+)= 200001
          and TargetCCost.VersionID (+)= 1088
          ) UpperSeqMapping,
          (
          select  Mapping.SourceCompID, sum(TargetCCost.Cost) as
TotalTargetCost
          from
          ComponentCost TargetCCost,
          CompToComp Mapping
          where Mapping.SourceCompID in (select compid from component where
compid >=5000000 and compid <=5099999 and sequence = 2 and SequenceType='C')
          and Mapping.PeriodID = 200001
          and Mapping.VersionID = 1088
          and TargetCCost.CompID = Mapping.TargetCompID
          and TargetCCost.PeriodID = 200001
          and TargetCCost.VersionID = 1088
          group by Mapping.SourceCompID
          ) TotalTargetCostTable
          where UpperSeqMapping.SourceCompID  =
TotalTargetCostTable.SourceCompID
      )
      where sourcecompid = ctoc.sourcecompid
      and targetcompid = ctoc.targetcompid

    )
    where /* exists
    (

          select UpperSeqMapping.SourceCompID, UpperSeqMapping.TargetCompID, UpperSeqMapping.SourceCost, decode(UpperSeqMapping.TargetCost, null, 0, UpperSeqMapping.TargetCost) as TargetCost, TotalTargetCostTable.TotalTargetCost

          from
          (
          select  decode(SourceCCost.Cost, null, 0, SourceCCost.Cost) as

SourceCost, Mapping.SourceCompID, Mapping.TargetCompID as TargetCompID, TargetCCost.Cost as TargetCost

          from
          ComponentCost SourceCCost,
          ComponentCost TargetCCost,
          CompToComp Mapping
          where Mapping.SourceCompID in (select compid from component where

sequence =2 and compid >=iSourceCompIDStart and compid <=iSourceCompIDEnd and SequenceType='C')

          and Mapping.PeriodID = 200001
          and Mapping.VersionID = 1088
          and SourceCCost.CompID (+)= Mapping.SourceCompID
          and SourceCCost.PeriodID (+)= 200001
          and SourceCCost.VersionID (+)= 1088
          and TargetCCost.CompID(+) = Mapping.TargetCompID
          and TargetCCost.PeriodID (+)= 200001
          and TargetCCost.VersionID (+)= 1088
          ) UpperSeqMapping,
          (
          select  Mapping.SourceCompID, sum(TargetCCost.Cost) as
TotalTargetCost
          from
          ComponentCost TargetCCost,
          CompToComp Mapping
          where Mapping.SourceCompID in (select compid from component where

compid >=iSourceCompIDStart and compid <=iSourceCompIDEnd and sequence = iSequence and SequenceType='C')

          and Mapping.PeriodID = 200001
          and Mapping.VersionID = 1088
          and TargetCCost.CompID = Mapping.TargetCompID
          and TargetCCost.PeriodID = 200001
          and TargetCCost.VersionID = 1088
          group by Mapping.SourceCompID
          ) TotalTargetCostTable
          where UpperSeqMapping.SourceCompID  =
TotalTargetCostTable.SourceCompID
          and UpperSeqMapping.SourceCompID = ctoc.sourcecompid
          and UpperSeqMapping.TargetCompID = ctoc.targetcompid

    )
    and*/ versionid=1088
    and periodid=200001
    and sourcecompid between 5000000 and 5099999     and targetcompid between 6000000 and 6099999;

    commit;

end Test;

Thanks,
Dwayne

Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message news:956695356.22389.0.pluto.d4ee154e_at_news.demon.nl...
> Without looking at your code we can't clarify the confusion.
> One general note: PL/SQL is very picky about scope and far more picky than
> sql*plus or sql*worksheet.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> Dwayne Remekie <dremekie_at_home.com> schreef in berichtnieuws
> 3905fe4a_3_at_news1.prserv.net...
> > Folks, I am confused.
> >
> > I have one fairly long and complicated UPDATE statement. When I run the
> > statement in SQL window, it works perfectly (153 rows updated with
 correct
> > values). I created a procedure with the EXACT same query in it and
 nothing
> > else, it does not work (all rows have wrong values).
> >
> > Also, The procedure has one parameter. The outcome of the update
 statement
> > changes when I change the parameter.... however the update statement
 does
> > not use ANY variables at all... it is hardcoded!
> >
> > It seems like a memory issue to me.
> >
> > What would cause an SQL statement to behave differently in a procedure
 than
> > when run in an SQL window? Is there some memory settings that I should
 be
> > looking at?
> >
> > Thanks for any help,
> > Dwayne
> >
> >
> >
>
>
Received on Tue Apr 25 2000 - 00:00:00 CDT