Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Help with PL/SQL

Re: Help with PL/SQL

From: Nascar <nascar_at_flash.net>
Date: Sat, 09 Sep 2000 15:41:56 GMT
Message-ID: <8Tsu5.804$oc3.77670@news.flash.net>

Sounds like a variable used in the day_year calculation might be wrong. Have you run the procedure through Procedure Builder and debugged it?

"angelmoon" <jfizzard_at_my-deja.com> wrote in message news:8pb7bt$58u$1_at_nnrp1.deja.com...
> Hi,
>
> I have a procedure wrote below that uses a cursor to select 2 column
> dates (order_return and return_date) from a rows in a table which are
> between a begin_date and an end_date. I have calcuated the number of
> days between the two dates(from the table).
> VIA
> day_year := (to_number(v_time.return_date - v_time.order_date));
> I want to be able to add the number of days found in that row to the
> number of days in the next row, but can not seem to be able to do so.
> It will get the number of days and insert it into day_years, but when
> calling
> number_days := number_days + day_year;
> directly after it, number_days = nothing at all. I am COMPLETELY
> baffled.
> Any suggestions on how to fix this?
> Below is the procedure which I have created. I have indicated the
> problem area with ****.
>
> (begin_date in out date,
> end_date in out date)
> as
> l_market_region varchar2(6);
> l_product_line varchar2(6);
> l_order_date date;
> l_return_date date;
> l_repair_out_date date;
> l_avg_return number;
> day_year number;
> total_days number;
> number_days number;
>
>
> CURSOR c_market_region IS
> select unique market_region
> from serial_table
> where (order_date >= begin_date
> and order_date <= end_date)
> or (return_date >= begin_date
> and return_date <= end_date)
> or (repair_out_date >= begin_date
> and repair_out_date <= end_date)
> order by market_region ASC;
>
> CURSOR c_product_line IS
> select unique product_line, market_region
> from serial_table
> where (order_date >= begin_date
> and order_date <= end_date)
> or (return_date >= begin_date
> and return_date <= end_date)
> or (repair_out_date >= begin_date
> and repair_out_date <= end_date)
> order by product_line ASC;
> CURSOR c_time_line IS
> select product_line, market_region, order_date,
> return_date,repair_out_date
> from serial_table
> where (return_date >= begin_date
> and return_date <= end_date)
> order by product_line ASC;
>
> begin
> l_avg_return := 0;
>
> for v_market_region in c_market_region loop
> htp.tablerowopen;
> htp.tabledata('Market Region '||v_market_region.market_region);
> htp.tablerowclose;
> for v_product in c_product_line loop
>
> if v_product.market_region = v_market_region.market_region then
> htp.tablerowopen;
> htp.tabledata(' '||' '||' Product Line '||
> v_product.product_line);
> htp.tabledata('');
>
> for v_time in c_time_line loop
> if v_time.product_line = v_product.product_line and
> v_time.market_region = v_market_region.market_region then
> ****
> day_year := (to_number(v_time.return_date -
> v_time.order_date));
> number_days := number_days + day_year;
> ***
> --raise_application_error(-
> 20000,number_days||' '||day_year||to_char(v_time.return_date)||to_char
> (v_time.order_date));
> end if;
>
> end loop;
> htp.tabledata('DETAIL ');
> htp.tabledata('');
> htp.tabledata('DETAIL'||number_days);
> htp.tablerowclose;
> end if;
> end loop;
>
> end loop;
>
> end;
>
> --
> Angelmoon
> jmfizz_at_my-deja.com
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sat Sep 09 2000 - 10:41:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US