Re: Having trouble with a query

No need to clutter up the dictionary with functions that are useful for a small number of select statements in the application. Instead, you can just use 'in-line views' and accomplish it all in one statement:

select t.lname, t.fname, v.sum(balance_due) tot_bal_due   from
(select adult_ssn ssn, balance_due

   from the_table
   where adult_ssn is not null
 union
 select ssn, balance_due
   from the_table
   where adult_ssn is null
) v
  where t.ssn = v.ssn
  group by t.lname, t.fname
;

Nick Butcher wrote:

> 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
Received on Mon Dec 07 1998 - 06:43:19 CST