Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2005
    Posts
    24

    Angry Unanswered: PL/SQL Package loop elsif trouble!

    hi i am updating end dates of an item for a person in our system based on the start date of the next item
    eg

    START DATE END DATE
    row1 05-05-06 19-04-05
    row2 06-09-06

    so row 1 end datre would be updated to 05-09-06

    i have managed to do this. but what i cant figure out is when i come to the last row i want it to just put the end date as one day after the start date so row2 end date would be 07-09-06.

    package below :
    DECLARE
    CURSOR c1 IS
    SELECT
    dbr_rac_accno
    FROM DEBIT_BREAKDOWNS,
    REVENUE_ACCOUNTS
    WHERE
    dbr_start_date > dbr_end_date
    AND dbr_status = 'A'
    AND dbr_rac_accno = rac_accno
    AND dbr_ele_code = 'HSYS'
    AND dbr_rac_accno = 70053;
    --
    l_refno NUMBER(8);
    --

    CURSOR c2 IS
    SELECT
    rowid,dbr_start_date, dbr_end_date
    FROM DEBIT_BREAKDOWNS,
    REVENUE_ACCOUNTS
    WHERE
    dbr_start_date > dbr_end_date
    AND dbr_status = 'A'
    AND dbr_rac_accno = rac_accno
    AND dbr_ele_code = 'HSYS'
    AND dbr_rac_accno = l_refno
    ORDER BY dbr_start_date DESC;
    --
    l_rowid ROWID;
    l_start_date DATE;
    l_end_date DATE;
    l_next_start_date DATE;
    CURSOR c3 IS
    SELECT
    MAX(dbr_start_date)
    FROM DEBIT_BREAKDOWNS,
    REVENUE_ACCOUNTS
    WHERE
    dbr_start_date > dbr_end_date
    AND dbr_status = 'A'
    AND dbr_rac_accno = rac_accno
    AND dbr_ele_code = 'HSYS'
    AND dbr_rac_accno = l_refno;

    l_max_start_date DATE;

    BEGIN
    OPEN c1;
    FETCH c1 INTO l_refno ;
    --
    WHILE c1%FOUND LOOP
    l_rowid := NULL;
    l_start_date := NULL;
    l_end_date := NULL;
    l_next_start_date := NULL;
    --
    OPEN c2;
    FETCH c2 INTO l_rowid, l_start_date, l_end_date;
    --
    WHILE c2% FOUND LOOP
    IF l_next_start_date IS NOT NULL THEN
    UPDATE DEBIT_BREAKDOWNS
    SET dbr_end_date = l_next_start_date - 1
    WHERE rowid = l_rowid
    AND dbr_start_date <= (l_next_start_date - 1);
    END IF;
    --
    l_next_start_date := l_start_date;

    --
    FETCH c2 INTO l_rowid, l_start_date, l_end_date;
    END LOOP;
    --
    CLOSE c2;
    --
    FETCH c1 INTO l_refno;
    END LOOP;
    --
    CLOSE c1;
    --
    --COMMIT;
    END;


    i started to try puttin cursor c3 in .. but cant figure out where in the loop i need to put it?
    any help would be ace!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If I understood what you'dlike to accomplish, perhaps there's no need for (a complicated) PL/SQL procedure - you could use LAG or LEAD analytical functions which return value from previous and next record (respectively). Try to Google for those functions and examples; seeing your short input data set and explanation of what you'd want to have, perhaps you could do it in pure SQL.

  3. #3
    Join Date
    Oct 2005
    Posts
    24
    thing is this does what i want upto the point of when it reaches the last date...

    not quite sure how id do it in pure sql as its dealing with only a certain number of rent accounts and needs to be done like this.

    i know all i need to do is get the cursor into the if statment but unsure how to do that

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    How do you know it is the "last" row of a given dbr_rac_accno ?

    Ugh.. this can be done purely and easily in SQL as Littlefoot suggested.

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Firstly, whenever you use loops to update a table, alarm bells should be ringing. There are very few circumstances that you will need to do a table update using a loop. Check out asktom.oracle.com and read through some of this guru's posts. PL/SQL is terribly slow and inneficient in comparison to SQL. But enough talk, lets see some action...

    I beleive the following will solve your problem:

    Code:
    update debit_breakdowns db
    	set db.dbr_end_date = 
    		(
    			select 
    				ss.dbr_end_date 
    			from
    				(
    					select
    						rowid id, -- or some other pk/uk
    						dbr_rac_accno,
    						dbr_status,
    						dbr_ele_code,
    						dbr_start_date,
    						nvl(lead(dbr_start_date) over (partition by dbr_rac_accno order by dbr_start_date), dbr_start_date + 1) dbr_end_date
    					from
    						debit_breakdowns
    					where
    						dbr_status = 'A'
    						and dbr_ele_code = 'HSYS'
    						and dbr_rac_accno = 70053
    				) ss
    			where
    				ss.id = db.rowid
    		)
    	where
    		db.dbr_status = 'A'
    		and db.dbr_ele_code = 'HSYS'
    		and db.dbr_rac_accno = 70053;
    Note the use of the sub select and the use of the window function, "lead". I also noticed that you know what the rac_accno is already, so no need to join on revenue_accounts.

  6. #6
    Join Date
    Oct 2005
    Posts
    24
    the last entry will have the most recent date..

    what would u suggest in normal sql then?

  7. #7
    Join Date
    Oct 2005
    Posts
    24
    thanks dayneo nice and helpful

    will give that bit a go

  8. #8
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    What do you mean when you say "the last entry", what defines the order here ?

  9. #9
    Join Date
    Oct 2005
    Posts
    24
    just run my plsql package and it was quicker than the sql just provided ?

    any ideas?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •