CODEWITHSUNDEEP
oracle-databaseplsql
Dan C
Dan C

Reputation: 41

Using Cursor to define cursor

I currently am trying to access total owed and total paid for every expense from a financial database. To elaborate, say I have 5 expenses. Each expense has many items in it whose totals can be added up to get its expense's total owed. The expenses also has an amount that has already been paid. I need to display whether or not these numbers(owed and paid) are equal for each expense in the table. I have the following:

SET ECHO OFF
SET VERIFY OFF
SET SERVEROUTPUT ON

DECLARE
    paid NUMBER;
    owed NUMBER;
    ExpNumber NUMBER;

    CURSOR CurrentNum IS
    SELECT ExpNum FROM ExpMast;
    CURSOR walkthrough IS

        SELECT SUM(Amt)
        FROM ExpDet WHERE ExpNum = ExpNumber;

    CURSOR wlkthr IS 
        SELECT SUM(CashAmt+Amt) FROM
        ExpMast NATURAL JOIN ExpByCC WHERE ExpNum = ExpNumber;

BEGIN
    OPEN walkthrough;
    OPEN wlkthr;
    OPEN CurrentNum;
    LOOP
        FETCH walkthrough INTO owed;
        FETCH wlkthr INTO paid;
        FETCH CurrentNum INTO ExpNumber;
        EXIT WHEN CurrentNum%NOTFOUND;  
        DBMS_OUTPUT.PUT_LINE('Calculating expenses for Exp: ' || ExpNumber);
            IF paid = owed THEN
                DBMS_OUTPUT.PUT_LINE('All Expenses paid. Total: ' || owed);

            ELSE
                DBMS_OUTPUT.PUT_LINE('Amount Paid: ' || paid);
                DBMS_OUTPUT.PUT_LINE('Total Owed: ' || owed);
                DBMS_OUTPUT.PUT_LINE('Difference: ' || (owed-paid));
            END IF;              
    END LOOP;
    CLOSE walkthrough;
    CLOSE wlkthr;
    CLOSE CurrentNum;
END;
/
SET VERIFY ON
SET ECHO ON

However when I run this, it shows the owed and paid for each expense as null. Any ideas as to where I am going wrong with my code?

Upvotes: 0

Views: 39

Answers (2)

APC
APC

Reputation: 146239

You could implement this in a single SQL query

select em.ExpNum 
          , nvl(ed.owed, 0) as owed
          , nvl(ecc.cc_paid, 0) + em.CashAmt as paid
          , nvl(ed.owed, 0) - (nvl(ecc.cc_paid, 0) + em.CashAmt) as diff
from ExpMast em
        left join ( select ExpNum, sum(Amt) as owed 
                    from ExpDet 
                    group by ExpNum ) ed
            on ed.ExpNum  = em.ExpNum  
        left join  ( select ExpNum, sum(Amt) as cc_paid 
                    from ExpByCC 
                    group by ExpNum ) ecc
            on ecc.ExpNum  = em.ExpNum    
order by em.ExpNum

This code uses outer joins, which allows for expenses to be monitored before details have been submitted, and optionally handling of credit card payment. Obviously I have had to make a couple of assumptions about your table structure and data, so you may need to tweak this code to make it work as you need.

Here is a SQL Fiddle.

Also, you should avoid using natural joins. Yes the ANSI SQL standard supports them, and yes it's useful to know they exist. But in real life they're just a bug waiting to happen.

Upvotes: 1

user8406805
user8406805

Reputation:

In your code there is a minor mistake of the sequences of opening the cursor, I have corrected all of them. The cursor "CurrentNum" is the driver cursor for other cursors so, you have to open two other cursor inside the loop of driver cursor. Below is the correct one, check out hopefully, it solves your purpose.

DECLARE
    paid NUMBER;
    owed NUMBER;
    ExpNumber NUMBER;

CURSOR CurrentNum IS
    SELECT ExpNum FROM ExpMast;

    CURSOR walkthrough IS
        SELECT SUM(Amt)
        FROM ExpDet WHERE ExpNum = ExpNumber;

    CURSOR wlkthr IS
        SELECT SUM(CashAmt+Amt) FROM
        ExpMast NATURAL JOIN ExpByCC WHERE ExpNum = ExpNumber;
BEGIN
    OPEN CurrentNum;
    LOOP
        FETCH CurrentNum INTO ExpNumber;
        EXIT WHEN CurrentNum%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Calculating expenses for Exp: ' || ExpNumber);

        OPEN walkthrough;
        FETCH walkthrough INTO owed;

        OPEN wlkthr;
        FETCH wlkthr INTO paid;

        IF paid = owed THEN
                DBMS_OUTPUT.PUT_LINE('All Expenses paid. Total: ' || owed);

            ELSE
                DBMS_OUTPUT.PUT_LINE('Amount Paid: ' || paid);
                DBMS_OUTPUT.PUT_LINE('Total Owed: ' || owed);
                DBMS_OUTPUT.PUT_LINE('Difference: ' || (owed-paid));
            END IF;
        CLOSE walkthrough;
        CLOSE wlkthr;
    END LOOP;
    CLOSE CurrentNum;
END;

Upvotes: 0

Related Questions