Re: Outer Join - Solution Needed

From: Rip McManus <rip_mcmanus_at_ccmail.turner.com>
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 edited
numeric 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

Original text of this message