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)
WHERE cnt BETWEEN 1 AND 10;

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!!

<amerar (AT) iwc (DOT) net> a écrit dans le message de news: 73d9495e-79aa-4b19-9923-c690c4057439...oglegroups.com...
order by new_table.column nulls last, ...
or
order by decode(new_table.column, null, 1, 0), ...

Regards
Michel

On Mar 22, 12:50*am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
I can give that a try, but how is that going to 'force' the values
from the extra table to the top? Say I have these values:

TABLE 1
John Doe 34
Robert Smith 27
Jeff Jones 42

Now, I select those from a table and order it by their age. Forget
about joining for now. Now, say the special parameter gets passed
into the package so I need to add the extra table which contains the
following:

TABLE 2
Kevin Johnson 36

I do not care about the sort order for the ending result set, I want
him at the top of the result set, above any other record.......over
riding the sort order.....

I do not think that is easily done without duplicating the entire long
select statement with some sort of UNION and weird sorting. I'm
hoping to avoid that.......

On Mar 22, 12:10*pm, John Schaeffer <ame... (AT) iwc (DOT) net> wrote:
Yes, you'll likely need a UNION query but you might not need
additional sorting; a UNION ALL, with the queries in the 'proper'
order (meaning your additional criteria table data listed first in the
UNION ALL construct) should return the additional results first in the
list. See here:

http://oratips-ddf.blogspot.com/2008...ing-union.html


David Fitzjarrell

On Mar 22, 2:26*pm, ddf <orat... (AT) msn (DOT) com> wrote:
Seems to be an interesting link. I'm playing with the query a bit to
see if I can tweak it. But, this is not an easy thing.

On Mar 22, 2:26*pm, ddf <orat... (AT) msn (DOT) com> wrote:
I might add, something else that makes this nearly impossible is that
the client wants to reture 10 records at a time, or all the records at
once. If the webpage is requesting the first 10 records, or all at
once, then the results from the additional table need to appear at the
top, pushing off any other records at the bottom of the records set to
make the result 10 records.

On Mar 22, 2:26*pm, ddf <orat... (AT) msn (DOT) com> wrote:
Triple post, sorry. But I think I found it. Basically a structure
like this:

SELECT .....
FROM (
SELECT....ordr, ROW_NUMBER() OVER (ORDER BY ordr, .......) cnt
FROM (
SELECT ......, 'A' ordr
FROM table1, table2, table3
WHERE ......)
UNION
SELECT ....., 'B'
FROM table1, table2
WHERE .....))
WHERE cnt BETWEEN 1 AND 10;

Using a hard coded value within the inner queries to force the sorting
order. Ugly, but I guess it will have to do......

John Schaeffer schreef:
I think this is quite a normal way of (un)sorting joins.... adding a
'source' column

Shakespeare