Re: Having trouble with a query

From: Nick Butcher <nickb_at_btinternet.com>
Date: Mon, 07 Dec 1998 11:11:09 +0000
Message-ID: <366BB7CD.7997@btinternet.com>

Derek

Create a function that returns the total balance for the adult and his/her dependents:

CREATE OR REPLACE FUNCTION test_function (p_ssn IN VARCHAR2) RETURN NUMBER AS
  total_balance NUMBER;
  CURSOR c_total IS
    SELECT SUM(balance_due)

      FROM test_table
     WHERE ssn = p_ssn
        OR adult_ssn = p_ssn;   

BEGIN
    OPEN c_total;
    FETCH c_total INTO total_balance;
    CLOSE c_total;
    RETURN total_balance;
END;
/

The query is then:

SELECT lname, fname, test_function (ssn)   FROM test_table
 WHERE adult_ssn IS NULL;

LNAME FNAME TEST_FUNCTION(SSN)

-------- -------- ------------------
Cass     Gary                      5
Archer   Peter                   2.5
Borland  Kevin                   1.5

Regards

Nick

Derek McDonald wrote:
>
> I am having a ^$#%# of a time with a query for a project.
>
> The table that I have looks like this:
>
> Lname Fname SSN Adult_SSN Balance_due
> -------- -------- ----------- ----------- ------------
> Cass Gary 222-33-4444 4
> Cass Donald 333-44-5555 222-33-4444 0
> Archer Peter 828-22-9876 0
> Archer Sue 987-66-5544 828-22-9876 2.5
> Cass Mark 777-88-9966 222-33-4444 1
> Borland Kevin 555-22-1114 1.5
> Borland Jean 555-22-4441 555-22-1114 0
>
> The entries with the adult_ssn's are children that the parent
> (the entry with the same ssn as the adult_ssn) are responsible
> for. I need to show the lname, fname, and sum of balances due
> for the parent, with the balances due of their kids included.
> I have been working on this one for many hours, and either am having
> a problem where it is counting the balance of Cass double, or since I
> have to show the fname also, grouping it by lname, fname. I know that I
> should use a count somewhere in the join to combine the kids
> balance_due, but haven't been able to figure this one out.
>
> the final outcome should look like this
>
> Lname Fname sum(balance_due)
> ------ ------ ---------------
> Archer Peter 2.5
> Borland Kevin 1.5
> Cass Gary 5
>
> Does anyone out there know how to solve this one?
Received on Mon Dec 07 1998 - 05:11:09 CST