Help with PL/SQL

From: angelmoon <jfizzard_at_my-deja.com>
Date: Fri, 08 Sep 2000 17:24:39 GMT
Message-ID: <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_at_my-deja.com


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

Received on Fri Sep 08 2000 - 12:24:39 CDT