6 Replies Latest reply: Nov 20, 2012 6:21 AM by 924460 RSS

    using order by in Union ALL

    924460
      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
          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
            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
              ...and your code tags are incorrect.
               
              
              Put one before and one after your code.                                                                                                                                                                                    
              • 4. Re: using order by in Union ALL
                Frank Kulash
                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
                  >
                  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
                    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.