Re: Having trouble with a query

OOPS - made an error! - here is the correction

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, the_table t
  where t.ssn = v.ssn
  group by t.lname, t.fname
;

"KeyStroke (Jack L. Swayze Sr.)" wrote:

> 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:51:37 CST