Union Operator

[jayeshdba]

Hello ,
I want to do a query using union operator and retrieve data from multiple tables (which fullfills condition to use union operator) ,
Now when i do a query i want to retrieve 25 rows only
from any table in a random manner .
But how can i bound this limit ? Any suggestions highly appreciated .

Jayesh

[tamilselvan]

Random manner does not work in Oracle. There should be some condition in the WHERE clause.

[vbaskar]

u need to plug in the rownum clause in the where condition and play with the values..thatz all u can do

[jayeshdba]

Is it possible if select query written inside cursor and can i bound the limit ?

[chrisrlong]

You may want to be a little clearer about what you actually need.

Originally posted by jayeshdba
I want to do a query using union operator and retrieve data from multiple tables (which fullfills condition to use union operator) ,

Just so you are not confused - you certainly do not need to be coming from multiple tables to use the UNION operator. Since you are confused on this point, I'm little worried that you may be thinking that you need the UNION when you don't. If you give us a little more info about the query, or the query itself, we could probably help you more.

Originally posted by jayeshdba
Now when i do a query i want to retrieve 25 rows only
from any table in a random manner .

This does not sound like a good requirement. In general (regardless of a UNION or not), when someone says something like that, what they *really* want to do is:

"I want to show the user 25 records. They can then page down through the result set." Further, when pressed, they usually realize that the data does indeed need to be sorted by *something* - usually some date/time field.

So what we *generally* have is a windowing issue. We want to show the user a 25-row window at a time. They can then page up and down, moving that window through the record set.

So, in general, the answer to the question is this:

SELECT
---OUTER.C1
FROM
---(
------SELECT
---------INNER.C1,
---------ROWNUM
------------AS QUERY_ROWNUM
------FROM
---------(
---------SELECT
------------C1
---------FROM
------------TABLE1
---------ORDER BY
------------C1
---------) INNER
------WHERE
---------ROWNUM < 8
---) OUTER
WHERE
---OUTER.QUERY_ROWNUM >= 4


...where:

---------SELECT
------------C1
---------FROM
------------TABLE1
---------ORDER BY
------------C1


is repaced by your query - UNION or not. Also, your values for ROWNUM and QUERY_ROWNUM would be 25 apart: 1/25. 26/50, etc.

Does this all make sense?

- Chris

[chrisrlong]

This thread has bee superceded by the following thread:

http://www.dbasupport.com/forums/sho...threadid=11539

- Chris