Re: PL/SQL question

In article <B63BF32C.11B8%ebycer_at_mediaone.net>,   Eric Bycer <ebycer_at_mediaone.net> wrote:
> Hey,
>
> > In article <B63B2FED.CC7%ebycer_at_mediaone.net>,
> > Eric Bycer <ebycer_at_mediaone.net> wrote:
> >> Hey,
> >>
> >> I have to write a procedure in Oracle 7.1.31 that uses the
 following
 two
> >> tables:
> >>
> >> table tbl_wstr(
> >> wstr number,
> >> status varchar2,
> >> statuscode varchar2,
> >> ....
> >> )
> >>
> >> and
> >>
> >> table statushistory(
> >> wstr number,
> >> status varchar2,
> >> statuscode varchar2,
> >> statusdate date
> >> )
> >>
> >> Where table tbl_wstr is joined in a one-to-many relationship with
> >> statushistory. Each time a wstr changes status or statuscode, a
 new
 row is
> >> appended to the statushistory table.
> >>
> >> What I need to do is write a procedure to calculate the days each
 wstr
> >> remains at each status.
>
> <snip of pseudocode>
>
> >> Obviously, I would enhance this with code for the statuscode, but I
 think
> >> you get the gist of what I am trying to do. If not, and you want
 to
 help,
> >> please do not hesitate to e-mail me.
> >>
> >> I've looked into cursors, PL/SQL tables, records, and I can't
 figure
 out how
> >> to this. I spent all day trying to figure out how to even get
 started.
> >> Any help would be greatly appreciated.
> >>
> >> Again, if you have any questions, please feel free to e-mail me.
> >>
> >> Thank you very much in advance.
> >>
> >> Eric Bycer
> >>
> >>
> > Hi Eric,
> >
> > You should be capable of getting this info using *1* single select.
> > This one:
> >
> > select wstr, status, trunc(max(statusdate) - min(statusdate)
> > from statushistory
> > group by wstr, status
> > /
>
> Actually, that is what we do now, but any wstr can go *back* in
 status (ie
> from approved to under investigation, or from analyzed to under
> investigation), so simply choosing the first date that something is at
> status and the last date it is at status is inaccurate, and that is
 what I
> have been tasked to correct.
>
> Also, there is a "deferred" status, and the entire time that
 something is at
> deferred should not be counted against the time a wstr is open.
 Basically,
> once something is deferred, restart the counter.
>
> It also gets really complex when the statuscodes are implemented, as
 there
> are about a dozen statuscodes for "Approved" and they go back and
 forth a
> LOT more often than status does.
>
> All I need is a way to implement the basic algorithm, and then I can
 make
> the enhancements myself (it wouldn't be fair of me to ask you all to
 do my
> work for me...)
>
> The key problem is seeing one row ahead in the statushistory table.
 if I
> were doing this in C/C++, I'd use 2 for loops with index i,j and just
 ask
> for statushistory.wstr[i,j] and statushistory.wstr[i,j+1], but I
 can't do
> that.
>
> > BTW why are you running such a prehistoric Oracle version? Oracle 7
> > will be desupported completely in a few weeks and 7.1 has been
 desupped
> > several years ago.
>
> Because that is what my company uses. I have no influence over that
 at all
> (I would like to see them upgrade to 8i because of its OOP ability,
 but...)
>
> > Regards,
> >
> > --
> > Sybrand Bakker, Oracle DBA
>
> Thanks for your help, and sorry for the confusion.
> Again, any help would be appreciated.
>
> Thanks,
> Eric Bycer
>
>

OK,
Understood the conceptual problem.
As I consider this an interesting challenge, I will setup your tables in my database and try to get it working with pl/sql and without pl/sql (just to see whether it is possible at all) Expect to hear from me today or tomorrow.

Regards,

--
Sybrand Bakker, Oracle DBA

All standard disclaimers apply
------------------------------------------------------------------------


Sent via Deja.com http://www.deja.com/
Before you buy.