This discussion is archived
12 Replies Latest reply: Nov 22, 2012 7:21 PM by rp0428 RSS

Union All

379746 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    You didn't mark any answer as correct / helpful ?
  • 7. Re: Union All
    sb92075 Guru
    Currently Being Moderated
    Handle:     AA
    Status Level:     Newbie
    Registered:     Nov 29, 2000
    Total Posts:     385
    Total Questions:     96 (46 unresolved)

    How SAD!
  • 8. Re: Union All
    379746 Newbie
    Currently Being Moderated
    Why so SAD?
  • 9. Re: Union All
    sb92075 Guru
    Currently Being Moderated
    AA wrote:
    Why so SAD?
    You get answers to less than half of your questions.
  • 10. Re: Union All
    379746 Newbie
    Currently Being Moderated
    I am sorry..I didn't get your point. Perhaps you could explain in a little more detail.
  • 11. Re: Union All
    rp0428 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    I am sorry...I will go back and mark the posts...

Legend

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