Re: Outer Join - Solution Needed
Date: 1996/12/08
Message-ID: <01bbe51b$b2f99780$1f65a69d_at_rips_home_pc.turner.com>#1/1
I saw another response to this, but the answer depends on what you want to do with the unmatched rows. If you want to return them, you need an outer join. If you want to eliminate them, a normal equijoin will give you the right result set. If you do want to return investments without a market price (and the price field returned as null), or with the latest price if there is a match, the prior response has a couple of minor errors in it. There was no "m1" and the subquery returns only one row, so the SQL should be:
select i.invest_id, m.market_price
from investment i, market_price_hist m
where i.invest_id = m.invest_id(+)
and m.date_field = (select max(date_field)
from market_price_hist m2 where m2.invest_id = m.invest_id)
If you want to tweak the result set, you could use NVL or DECODE on m.market_price. Assuming m.market_price is a numeric data type, for example:
select i.invest_id, nvl(m.market_price, 0)...
Returns 0 when unmatched
select i.invest_id, decode(m.market_price, null,
' NO MATCH', to_char(m.market_price, '99,990.000'))... Returns the words 'NO MATCH' right-justified in the editednumeric field
Ripperm_at_aol.com
Shivakumar Rajagopal <KMAQ65A_at_prodigy.com> wrote in article
<58dhki$8dg_at_usenetw1.news.prodigy.com>...
> investment is a table with invest_id as a key field. I need to list all
> invest_ids with their latest market price from market_price_hist. Mind
> you every invest_id need not be in market_price_hist. Also there are
> multiple rows for each invest_id in market_price_hist.
>
> Shivakumar
>
> srajago774_at_aol.com
>
>
Received on Sun Dec 08 1996 - 00:00:00 CET