Re: Query & Column Display

From: <mrdjmagnet_at_aol.com>
Date: Thu, 22 Jan 2009 11:54:37 -0800 (PST)
Message-ID: <575ce9dd-1c9b-46f6-a000-175420330b49_at_40g2000prx.googlegroups.com>

On Jan 22, 11:51 am, Ed Prochak <edproc..._at_gmail.com> wrote:
> On Jan 22, 12:12 pm, mrdjmag..._at_aol.com wrote:
>
> > I did this once before a long time ago, but cannot remember how.
>
> > Say I have 2 tables.  I query table #1, and if the value I am looking
> > for is not there, then get the value from table 2.
>
> > Both tables have the same primary key, so they must be joined.  I
> > remember this was NOT a lot of code with subselects and all that
> > jazz.  But I cannot recall how it was done.
>
> > Was looking at analytical stuff for this, but no luck yet......
>
> > Thanks!
>
> NVL() ??
>
> select nvl(table1.val, table2.val) valueLookingFor
> from table1, table2
> where table1.key = table2.key.
>
> HTH,
>   Ed

Well, the query is actually much more complicated than that......because of the joins between the tables:

SELECT fund_name, (''||ticker||'') ticker, NVL(curr_rank, performance_rank) curr_rank, nav,

       total_return_1yr, total_return_3yr, total_return_5yr, performance_rank
FROM (
  SELECT mf.fund_name, mf.ticker, fr.curr_rank, dp.nav,

         fm.price_earnings_avg, fm.total_return_1yr, fm.total_return_3yr,

         fm.total_return_5yr, pr.performance_rank, ROW_NUMBER()   OVER (ORDER BY fr.curr_rank, fm.total_return_1yr DESC) AS id   FROM mutual_fund mf, fund_master fm, fund_rank fr,

       fund_daily_prices dp, mutual_fund.fund_styles fs, mutual_fund.performance_rank pr

  WHERE fm.fund_id = mf.m_fund_id
    AND mf.ticker = fr.ticker
    AND mf.ticker = dp.ticker
    AND mf.ticker = pr.ticker
    AND fr.curr_rank <= 5
    AND mf.ticker = fs.ticker
    AND fm.total_return_1yr IS NOT NULL
    AND fm.obj_descr = 'Dvsfd Bond' ) alltop

WHERE id BETWEEN 1 AND 10
ORDER BY curr_rank ASC, total_return_1yr DESC;

The tables in the problem here are: FUND_RANK and PERFORMANCE_RANK.

If the value is in FUND_RANK, use that. If not, use the one from PERFORMANCE_RANK. But, this is complicated by the criteria and join in the WHERE clause.... Received on Thu Jan 22 2009 - 13:54:37 CST