Re: Query & Column Display

From: <>
Date: Thu, 22 Jan 2009 11:54:37 -0800 (PST)
Message-ID: <>

On Jan 22, 11:51 am, Ed Prochak <> wrote:
> On Jan 22, 12:12 pm, 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
  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
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

Original text of this message