12 Replies Latest reply: Nov 22, 2012 9:21 PM by rp0428 RSS

    Union All

    379746
      Hi,
      I have the following query where I am trying to get two different types of Orders and the objective is to get all order types in the first query plus all the order types in the second query and order the result by header_id.
      Is UNION ALL the best way to do it? Also, how do I order the result by header_id.
      Any help would be appreciated.
      Regards
      A/A
      SELECT  *
        FROM  OE_order_lines_all hdr
        WHERE TRUNC(creation_date ) >= TRUNC(SYSDATE) - 30
        AND NOT EXISTS (SELECT 1
            FROM  oe_order_holds_all hold
            WHERE  hold.line_id = hdr.line_id
              AND  hold.RELEASED_FLAG = 'N')
          AND  FLOW_STATUS_CODE IN ('BOOKED','AWAITING_SHIPPING')  
         AND orig_sys_document_ref NOT LIKE '852%'
      AND hdr.line_type_id IN (1003)
          AND  hdr.header_id NOT IN (SELECT Header_id
                                      FROM T1
                                     WHERE record_status = 'PROCESSED'
                OR record_status = 'PICK')
         AND EXISTS (SELECT * FROM T2
             WHERE  E_855_OUTBOUND = 'Y'
               AND (CUSTOMER_NUMBER IN (SELECT LOCATION
                 FROM hz_cust_site_uses_all
                 WHERE site_use_id = hdr.INVOICE_TO_ORG_ID)
                 OR EXISTS (SELECT DISTINCT hdr.attribute2
      
                      FROM OE_order_lines_all 
                        WHERE hdr.attribute2 = BROKER_NUMBER))) 
                        
      UNION ALL
      SELECT  *
        FROM  OE_order_lines_all hdr
        WHERE TRUNC(creation_date ) >= TRUNC(SYSDATE) - 30
        AND NOT EXISTS (SELECT 1
            FROM  oe_order_holds_all hold
            WHERE  hold.line_id = hdr.line_id
              AND  hold.RELEASED_FLAG = 'N')
          AND  FLOW_STATUS_CODE IN ('ENTERED')  
         AND orig_sys_document_ref NOT LIKE '852%'
      AND hdr.line_type_id IN (1009)
          AND  hdr.header_id NOT IN (SELECT Header_id
                                      FROM T1
                                     WHERE record_status = 'PROCESSED'
                OR record_status = 'PICK')
         AND EXISTS (SELECT * FROM T2
             WHERE  E_855_OUTBOUND = 'Y'
               AND (CUSTOMER_NUMBER IN (SELECT LOCATION
                 FROM hz_cust_site_uses_all
                 WHERE site_use_id = hdr.INVOICE_TO_ORG_ID)
                 OR EXISTS (SELECT DISTINCT hdr.attribute2
      
                      FROM OE_order_lines_all 
                        WHERE hdr.attribute2 = BROKER_NUMBER)))
                      
                        
        • 1. Re: Union All
          812975
          if you need exact data from both the queries and want to merge then yes, but seems like you'll have duplicate data

          try this otherwise (not tested)
          SELECT  *
            FROM  OE_order_lines_all hdr
            WHERE TRUNC(creation_date ) >= TRUNC(SYSDATE) - 30
            AND ((NOT EXISTS (SELECT 1
                FROM  oe_order_holds_all hold
                WHERE  hold.line_id = hdr.line_id
                  AND  hold.RELEASED_FLAG = 'N')
              AND  FLOW_STATUS_CODE IN ('BOOKED','AWAITING_SHIPPING')  
             AND orig_sys_document_ref NOT LIKE '852%'
          AND hdr.line_type_id IN (1003)
              AND  hdr.header_id NOT IN (SELECT Header_id
                                          FROM T1
                                         WHERE record_status = 'PROCESSED'
                    OR record_status = 'PICK')
                 OR
               NOT EXISTS (SELECT 1
                FROM  oe_order_holds_all hold
                WHERE  hold.line_id = hdr.line_id
                  AND  hold.RELEASED_FLAG = 'N')
              AND  FLOW_STATUS_CODE IN ('ENTERED')  
             AND orig_sys_document_ref NOT LIKE '852%'
          AND hdr.line_type_id IN (1009)
              AND  hdr.header_id NOT IN (SELECT Header_id
                                          FROM T1
                                         WHERE record_status = 'PROCESSED'
                    OR record_status = 'PICK')))
             AND EXISTS (SELECT * FROM T2
                 WHERE  E_855_OUTBOUND = 'Y'
                   AND (CUSTOMER_NUMBER IN (SELECT LOCATION
                     FROM hz_cust_site_uses_all
                     WHERE site_use_id = hdr.INVOICE_TO_ORG_ID)
                     OR EXISTS (SELECT DISTINCT hdr.attribute2
           
                          FROM OE_order_lines_all 
                            WHERE hdr.attribute2 = BROKER_NUMBER))) 
           ORDER BY hdr.header_id
          • 2. Re: Union All
            rp0428
            >
            how do I order the result by header_id.
            >
            Wrap the UNION ALL result in another query and use ORDER BY header_id assuming header_id is the name of a column in the result set
            SELECT * FROM (
               SELECT * FROM TABLE1
               UNION ALL
               SELECT * FROM TABLE2
            )
            ORDER BY header_id
            • 3. Re: Union All
              812975
              I don't think we need to wrap the query. OP needs to write order by at the end of the query. thats it.
              DROP TABLE TEST1; DROP TABLE TEST2;
              CREATE TABLE TEST1 (COL VARCHAR2(1));
              INSERT INTO TEST1 VALUES ('D'); 
              INSERT INTO TEST1 VALUES ('X');
              INSERT INTO TEST1 VALUES ('F');
              INSERT INTO TEST1 VALUES ('G');
              
              CREATE TABLE TEST2 (COL VARCHAR2(1));
              INSERT INTO TEST2 VALUES ('J');
              INSERT INTO TEST2 VALUES ('T');
              INSERT INTO TEST2 VALUES ('E');
              INSERT INTO TEST2 VALUES ('W');
              
              SELECT * FROM TEST1
              UNION ALL
              SELECT * FROM TEST2
              ORDER BY 1;
              Edited by: SaadL on Nov 22, 2012 4:39 PM
              • 4. Re: Union All
                Frank Kulash
                Hi,
                AA wrote:
                ... Is UNION ALL the best way to do it? Also, how do I order the result by header_id.
                No, if both branches of the UNION reference the same table(s), then there's probably a more efficient way to get the same results without a UNION. By avoiding the UNION, you'll avoid problems like the one you're having with ORDER BY.

                Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements) for all the tables involved, so the people who want to help you can re-create the problem and test their ideas. Also post the results you want from that data, and an explanation of how you get those results 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: Union All
                  379746
                  Thank you all for your response.
                  I am getting 44 records if I use the UNION ALL or join the two queries by 'OR' and 29 records and 15 records if I run the queries individually. I will go with the 'OR' option and I can order the result by Header ID.
                  Thank you
                  A/A
                  • 6. Re: Union All
                    812975
                    You didn't mark any answer as correct / helpful ?
                    • 7. Re: Union All
                      sb92075
                      Handle:     AA
                      Status Level:     Newbie
                      Registered:     Nov 29, 2000
                      Total Posts:     385
                      Total Questions:     96 (46 unresolved)

                      How SAD!
                      • 8. Re: Union All
                        379746
                        Why so SAD?
                        • 9. Re: Union All
                          sb92075
                          AA wrote:
                          Why so SAD?
                          You get answers to less than half of your questions.
                          • 10. Re: Union All
                            379746
                            I am sorry..I didn't get your point. Perhaps you could explain in a little more detail.
                            • 11. Re: Union All
                              rp0428
                              >
                              I am sorry..I didn't get your point. Perhaps you could explain in a little more detail.
                              >
                              His point is that your user profile shows this
                              >
                              Total Questions: 96 (46 unresolved)
                              >
                              You still have 46 previous questions that have never been marked ANSWERED. Most forum volunteers select unanswered questions to try to help with. It is very frustrating if someone takes the time to select one of your 46 to try to help with and then, after reading it, it looks like you already got an answer, or simply abandoned the thread. That person just wasted their time when they could have been helping someone else.

                              Granted - most of those 46 are fairly old but it keeps the forum clean if you mark questions ANSWERED when it has been answered or even if you are no longer interested in the thread.

                              For examples, here is one of your threads from May 2009
                              APP-FND-01206 error

                              Your very last post was this
                              >
                              Thanks..That worked. I did try it before but wasnt sure if I wanted to go ahead...
                              >
                              So why didn't you mark that thread ANSWERED?

                              Please take the time to revisit your old threads and mark them ANSWERED if they have been or if you no longer need, or expect, help with them. If you do still need help them add a new reply so the thread will pop to the top of the list.
                              • 12. Re: Union All
                                379746
                                I am sorry...I will go back and mark the posts...