Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    52

    Unanswered: ORDER BY with UNION

    Hey!!!

    Hi all friends!!!


    I have a query which use an UNION word and this is like

    SELECT filed1 FROM table1
    UNION
    SELECT filed1 FROM table2
    ORDER BY field1

    it works find

    But if i make this in a Stored Procedure

    CURSOR IS
    SELECT filed1 FROM table1
    UNION
    SELECT filed1 FROM table2
    ORDER BY field1;

    It's bad

    But if i make this (it is if i dont write the Order By clause)


    CURSOR IS
    SELECT filed1 FROM table1
    UNION
    SELECT filed1 FROM table2;

    It works fine


    What is happennig??, does someone can help me please??


    ThanX for all



    Regards

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >SELECT filed1 FROM table2
    >ORDER BY field1;

    FWIW - field1 <> filed1
    They are spelled/typed differently.
    Could this be part of the problem?

  3. #3
    Join Date
    Nov 2003
    Posts
    52
    No, this is just a general example of my original query,
    The query i have more fields and more tables, in fact each query is a join of four tables.


    Any ideas??????



    Regards

  4. #4
    Join Date
    Nov 2003
    Posts
    52
    CURSOR IS
    SELECT field1 FROM table1
    UNION
    SELECT field1 FROM table2
    ORDER BY field1;


    This is the right example i typed wrong.

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Consider the code, Oracle doesn't know if the order by is part of your second query, or the last line of the entire query. Neither does anyone else looking at it. The result is ambiguous code. If a human can't decide, a parser certainly can't.

    PL/SQL is a great tool but it doesn't like ambiguity, this is probably why PL/SQL works so well.

    Rewrite your query like this and you may see what I mean by ambiguity....

    PHP Code:
    SELECT field1 FROM table1
    UNION
    SELECT field1 FROM table2 ORDER BY field1

    Some would say that Oracle should determine that the Order by is useless within a union such as this, some would disagree.

    Try this instead....
    PHP Code:
    SELECT from
       
    (
       
    SELECT field1 FROM table1
       UNION
       SELECT field1 FROM table2 
       
    )
    ORDER BY field1
    Hth
    Bill

    P.S. Please note the pubs have just closed in the UK.
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  6. #6
    Join Date
    Nov 2003
    Posts
    52

    Talking

    Oh yeah!!!!


    It worked : )


    ThanX Bill

Posting Permissions

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