Re: PL/SQL question

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.
>
> I have absolutely no idea how to write it for PL/SQL syntax-wise, but
 the
> basic pseudocode would be as follows:
>
> create or replace procedure days_calculator(v_status IN varchar2)
> AS
> DECLARE
> days_count number := 0;
> temp_start date;
> temp_date date;
> BEGIN
> for wstr in tbl_wstr loop
> for wstr in statushistory
> where tbl_wstr.wstr = statushistory.wstr loop
> day_count := 0;
> if statushistory.status = v_status
> then
> temp_start := statusdate from the current row;
> /* next row in statushistory has different status by definition
 */
> temp_end := statusdate from the next row;
> days_count := days_count + trunc(temp_end -
 temp_start);
> end if;
> end loop;
> insert into output_table(wstr, days, status)
> values (wstr, days_count, v_status);
> end loop;
> END;
>
> then I would call
>
> days_calculator('NEW')
> days_calculator('APPROVED')
> etc., etc.
>
> and then
>
> select round(avg(days)) from output_table group by status
>
> to get my final output.
>
> 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
/

If you want to insert that in a table (and frankly I don't see why, because it would mean storing redundant info) you could simply

insert into output_table
(wstr, status, days)
select wstr, status, trunc(max(statusdate) - min(statusdate) from statushistory
group by wstr, status
/

And that should be all there is.
No cursors, nothing!

If you have any questions and comments please e-mail them to oradba_at_sybrandb.demon.nl, as I don't regularly check my-deja e-mail. 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.

Regards,

--
Sybrand Bakker, Oracle DBA

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


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