This discussion is archived
6 Replies Latest reply: Nov 20, 2012 4:21 AM by 924460 RSS

using order by in Union ALL

924460 Newbie
Currently Being Moderated
hi Following is my query and am unable to accomplish my order by statement in Union All . please help me in this.

<code>


SELECT b.audit_trail_id,
TO_CHAR (b.last_update_date, 'DD-Mon-YYYY') "Revision Date",
(u.last_name || ', ' || u.first_name || ' ' || u.mi) "By",
b.col_name "Field",
b.new_value "New Value",b.previous_value "Old Value",
a.lot_id,
b.comment_text "Comments"
FROM clem_audit_trail b,lot a, users u

WHERE a.lot_id = b.lot_id(+)
AND u.user_id(+) = b.last_update_user_id
AND a.lot_id in (select lot_id from request_lot where request_id = 51914)
AND col_name IN
('Interim Release Date',
'Final Disposition Date',
'On Time Disposition',
'Intended Use',
'REJECTS_YN',
'# of QARs',
'# of Quality Events',
'# of LIRs',
'# of Change Controls',
'# of Batch Records',
'# of GMP Batch Records',
'EXPLANATION',
'QA Notes',
'GDMS_LINK')

AND UPPER (table_name) IN('LOT') order by b.last_update_date

UNION ALL

SELECT b.audit_trail_id,
TO_CHAR (b.last_update_date, 'DD-Mon-YYYY') "Revision Date",
(u.last_name || ', ' || u.first_name || ' ' || u.mi) "By",
b.col_name "Field",
b.new_value "New Value",b.previous_value "Old Value",
r.request_id,
b.comment_text "Comments"
FROM clem b,request r, users u

WHERE r.request_id = b.request_id(+)
AND u.user_id(+) = b.last_update_user_id
AND r.request_id = 51914
AND UPPER (table_name) IN ('REQUEST') AND
b.col_name IN ('Partial Release',
'All QP Docs in GDMS',
'Number of Batch Records',
'Request',
'QP Issues',
'Snag Comments',
'Responsible Group',
'Reason Late',
'Study Impact',
'Other Details',
'Request Status',
'QP Link to GDMS'
) order by b.last_update_date

</code>

If i execute individually it works fine, if i use union all it throughs error for me.

Any suggestion...
  • 1. Re: using order by in Union ALL
    sb92075 Guru
    Currently Being Moderated
    since we don't have your tables or data we can't run your code.

    ERROR? What ERROR? I don't see any error.

    How do I ask a question on the forums?
    SQL and PL/SQL FAQ

    Handle:     born2win
    Status Level:     Newbie
    Registered:     Mar 16, 2012
    Total Posts:     6
    Total Questions:     3 (2 unresolved)
  • 2. Re: using order by in Union ALL
    onedbguru Pro
    Currently Being Moderated
    when requesting assistance, actually providing the error that was "thrown" goes a long way in trying to solve your problem.

    Try wrapping the union all:

    select * from
    (select ... from ...
    union all
    select ... from ...
    ) order by ...
  • 3. Re: using order by in Union ALL
    SomeoneElse Guru
    Currently Being Moderated
    ...and your code tags are incorrect.
     
    
    Put one before and one after your code.                                                                                                                                                                                    
  • 4. Re: using order by in Union ALL
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    In a set operation (such as UNION) table aliases (such as b.) only have meaning within the branch in which they are defined. The ORDER BY clause is not part of any 1 branch, so a table alias can't be used there.
    In most cases, you can use a column name (or alias) from the first branch, like this:
    SELECT    b.audit_trail_id,
              TO_CHAR (b.last_update_date, 'DD-Mon-YYYY') "Revision Date",
    ...
    order by  "Revision Date"
    ;
    and you can always order by column number, like this:
    SELECT    b.audit_trail_id,
              TO_CHAR (b.last_update_date, 'DD-Mon-YYYY') "Revision Date",
    ...
    order by  2
    ;
    In this case, however, there's an extra problem. You're transforming the DATE to a string in the SELECT clause. The string is all that's available in the ORDER BY clause, and the string '19-NOV-2012' comes after the string '01-JAN-2015', because the character '1' comes after '0'. Perhaps the simplest solution is to have your front end, and not the query, format the dates. In SQL*Plus, you can do this:
    ALTER SESSION  SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
    
    SELECT    b.audit_trail_id,
              b.last_update_date    AS "Revision Date",
    ...
    order by  2
    ;
     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
    Explain, using specific examples, how you get those results from that data.
    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
  • 5. Re: using order by in Union ALL
    rp0428 Guru
    Currently Being Moderated
    >
    hi Following is my query and am unable to accomplish my order by statement in Union All
    >
    Don't use ORDER BY for each of the individual queries. You are going to do a UNION ALL so the ORDER BY on each query doesn't do any good anyway. Use the ORDER BY after the queries are unioned together like this:
    . . .
    SELECT * FROM 
    (SELECT * FROM T1
    UNION ALL
    SELECT * FROM T2)
    ORDER BY 1, 2
    . . .
  • 6. Re: using order by in Union ALL
    924460 Newbie
    Currently Being Moderated
    Hi Frank and everyone,

    Thanks for your attention and on my thread and frank's solution suited for me and i ma done my need.

    Appreciate all your time.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points