From: "Nascar" <nascar@flash.net>
Newsgroups: comp.databases.oracle.misc
References: <8pb7bt$58u$1@nnrp1.deja.com>
Subject: Re: Help with PL/SQL
Lines: 116
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.3018.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.3018.1300
Message-ID: <8Tsu5.804$oc3.77670@news.flash.net>
Date: Sat, 09 Sep 2000 15:41:56 GMT
NNTP-Posting-Host: 63.254.25.93
X-Complaints-To: abuse@flash.net
X-Trace: news.flash.net 968514116 63.254.25.93 (Sat, 09 Sep 2000 10:41:56 CDT)
NNTP-Posting-Date: Sat, 09 Sep 2000 10:41:56 CDT
Organization: FlashNet Communications, http://www.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@my-deja.com> wrote in message
news:8pb7bt$58u$1@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@my-deja.com
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.