Re: PL/SQL question

Hey,

> In article <B63B2FED.CC7%ebycer_at_mediaone.net>,
> Eric Bycer <ebycer_at_mediaone.net> wrote:

<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 Received on Sat Nov 18 2000 - 14:47:45 CET