Union Operator
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Union Operator

  1. #1
    Join Date
    Nov 2000
    Posts
    34

    Arrow

    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
    Share on Google+

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Random manner does not work in Oracle. There should be some condition in the WHERE clause.
    Share on Google+

  3. #3
    Join Date
    Jan 2001
    Posts
    153
    u need to plug in the rownum clause in the where condition and play with the values..thatz all u can do
    Vijay.s
    Share on Google+

  4. #4
    Join Date
    Nov 2000
    Posts
    34

    Arrow Union operator

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

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Share on Google+

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    This thread has bee superceded by the following thread:

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

    - Chris
    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width