Re: SQL: calculations with un-joined table

In article <362faf25.24693247_at_news.northgrum.com>,   mark_aurit_at_mail.northgrum.com (Mark Aurit) wrote:
> I an trying to figure out how to use an element from a
> table in a calculation, where the tables aren't joinable.
>
> Simplistically, I have a "Travel" table as follows:
> create table trip_master
> (trip_taker varchar2(10),
> daily_miles_driven number(6))
>
> and a "Travel Parameter" table as follows:
> create table trip_parm
> (annual_mileage_amount number(4,2))
>
> I want to create a view summarizing the first table,
> while calculating the trip reimbursement.
>
> So Ive tried
>
> select t.trip_taker,sum(t.daily_miles_driven),
> sum(t.daily_miles_driven)* MAX(p.annual_mileage_amount)
> from trip_taker t,table trip_parm p
> group by t.trip_taker
>
> But that obviously doesnt work, and Im now sure
> how to work this.
>
> Any help greatly appreciated.
> Mark
> mark_aurit_at_mail.northgrum.com
>

Use a subquery that gets the MAX value so that you are essentially joining against a temp table with only one row:

select t.trip_taker,t.total_miles, t.total_miles*p.mile_amount from
 ( select t.trip_taker,sum(t.daily_miles_driven) total_miles    from trip_taker
   group by t.trip_taker) t,
 ( select MAX(p.annual_mileage_amount) mile_amount

       from trip_parm ) p
 ;

That should work for you (forgiving any typos on my part).

--
Ed Prochak
Magic Interface, Ltd.
440-498-3702

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Oct 23 1998 - 10:15:25 CDT