Sorting Order by Table?

We have this query, which is actually put together dynamically within some PL/SQL code and opened via REF CURSOR. The WHERE clause is built using regular IF / THEN logic to put together the criteria and then read via REF CURSOR.

SELECT fund_name, ticker, class_rank, nav, total_return_ytd,

       total_return_1yr, total_return_3yr FROM (SELECT mf.fund_name, mf.ticker,

             class_rank, dp.nav, NVL(fm.total_return_ytd,

-9999999999) total_return_ytd,
             NVL(fm.total_return_1yr, -9999999999) total_return_1yr,
             NVL(fm.total_return_3yr, -9999999999) total_return_3yr ,
ROW_NUMBER()
      OVER (ORDER BY class_rank, total_return_6mt DESC) cnt
      FROM mutual_fund mf, fund_master fm, fund_daily_prices dp,
fund_styles fs,
           performance_rank pr, fund_classes fc
      WHERE fm.fund_id = mf.m_fund_id
        AND mf.ticker = dp.ticker(+)
        AND mf.ticker = pr.ticker(+)
        AND mf.ticker = fs.ticker(+)
        AND mf.ticker = fc.ticker(+)
        AND dp.nav IS NOT NULL
        AND fm.obj_descr = 'Growth'
      ORDER BY class_rank ASC, total_return_1yr DESC)

Here is the hard part: When certain parameters are passed into the code, I want to add another table to the FROM clause and another set of criteria to the WHERE clause. That is easy, BUT, the records which get pulled from the added table, those need to appear on top of the result set.

So, for example, say I am selecting people from a set of tables based on age, and ordering them by last name. Now, when certain criteria is passed, I want to select certain people from an additional table and put them at the top of the result set.

Not sure this can be done easily. I really want to avoid some huge UNION queries with some awkward sorting column.......

Thanks!! Received on Sat Mar 21 2009 - 19:39:04 CDT