title: Union of query with another query based on the first
body: <p>I want a union of one query with joins and another query which uses the output of first query as input.</p>
  
  <p>I tried creating a stored procedure and looping through a cursor and inserting data one by one in a temporary table but it is very slow. Let me know if you want to have a look at the procedure.</p>
  
  <p>My first query is:</p>
  
  <pre><code>SELECT od.ID,
    od.CUST_PO_NUM,
    od.CUST_STATUS,
    TO_CHAR(od.SHIP_DATE,'DD-MON-YY') AS SHIP_DATE,
    od.A_PART_NUM,
    TO_CHAR(REQUIRED_DATE,'DD-MON-YY')                                                       AS REQUIRED_DATE,
    SUM(NVL(ct.SHIPPED_QTY, 0))                                                              AS SHIPPED_QTY,
    TO_CHAR(od.CUR_ACKED_DATE,'DD-MON-YY')                                                   AS CUR_ACKED_DATE,
    TO_CHAR(od.DOCK_DATE,'DD-MON-YY')                                                        AS DOCK_DATE,
    TO_CHAR(od.POD_DATE,'DD-MON-YY')                                                         AS POD_DATE,
    od.SHIP_TO,
    od.UNIT_PRICE,
    od.ITEM_TOTAL,
    od.SHIP_FROM,
    od.REQUIRED_DATE_LASTCHG_DT,
    od.DOCK_DATE_LASTCHG_DT,
    od.CUST_STATUS_LASTCHG_DT,
    od.ROUTING_CODE,
    TO_CHAR(od.ENTERED_DATE,'DD-MON-YY') AS ENTERED_DATE,
    od.CUST_NUM,
    NVL(sd.SAD_ALERT_FLAG,'N') AS SAD_ALERT_FLAG,
    NVL(sd.SHIP_ALERT,'N')     AS SHIP_ALERT,
    sd.person_id,
    ct.DELIVERY_NUMBER,
    ct.CR_TRACK_NUM,
    NVL(od.CANCELLED_QUANTITY,'0') AS CANCELLED_QUANTITY,
    ct.CARRIER_NAME,
    ct.WAYBILL,
    ct.SHIP_METHOD_CODE
  FROM ORDER_DETAIL od,
    ALERTS sd,
    C_TRACKING ct
  WHERE od.ID  = sd.ID(+)
  AND od.ID    = ct.ID(+)
  AND od.CUST_NUM IN (10000) 
  AND OD.CUST_STATUS in ( 'BOOKED', 'SCHEDULED TO SHIP', 'CLOSED' , 'CANCELLED' , 'INVOICE_HOLD')
  GROUP BY od.ID,
    od.CUST_PO_NUM,
    od.CUST_STATUS,
    od.SHIP_DATE,
    od.A_PART_NUM,
    od.REQUIRED_DATE,
    od.CUR_ACKED_DATE,
    od.DOCK_DATE,
    od.POD_DATE,
    od.SHIP_TO,
    od.UNIT_PRICE,
    od.ITEM_TOTAL,
    od.SHIP_FROM,
    od.REQUIRED_DATE_LASTCHG_DT,
    od.DOCK_DATE_LASTCHG_DT,
    od.CUST_STATUS_LASTCHG_DT,
    od.ROUTING_CODE,
    od.ENTERED_DATE,
    od.CUST_NUM,
    NVL(sd.SAD_ALERT_FLAG,'N'),
    NVL(sd.SHIP_ALERT,'N'),
    sd.person_id,
    ct.DELIVERY_NUMBER,
    ct.CR_TRACK_NUM,
    NVL(od.CANCELLED_QUANTITY,'0'),
    ct.CARRIER_NAME,
    ct.WAYBILL,
    od.CUST_NUM,
    ct.SHIP_METHOD_CODE
  ORDER BY od.CUST_PO_NUM ASC
  </code></pre>
  
  <p>If I call the above query <code>query1</code>, then I want all the data returned by <code>query1</code> plus I want to fetch all the data from table <code>order_detail</code> where <code>parent_line_number</code> is the ID from <code>query1</code>.</p>
  
  <pre><code>SELECT ID,
    CUST_PO_NUM,
    CUST_STATUS,
    TO_CHAR(SHIP_DATE,'DD-MON-YY') AS SHIP_DATE,
    A_PART_NUM,
    TO_CHAR(REQUIRED_DATE,'DD-MON-YY')                                                       AS REQUIRED_DATE,
    NULL,
    TO_CHAR(CUR_ACKED_DATE,'DD-MON-YY')                                                   AS CUR_ACKED_DATE,
    TO_CHAR(DOCK_DATE,'DD-MON-YY')                                                        AS DOCK_DATE,
    TO_CHAR(POD_DATE,'DD-MON-YY')                                                         AS POD_DATE,
    SHIP_TO,
    UNIT_PRICE,
    ITEM_TOTAL,
    SHIP_FROM,
    REQUIRED_DATE_LASTCHG_DT,
    DOCK_DATE_LASTCHG_DT,
    CUST_STATUS_LASTCHG_DT,
    ROUTING_CODE,
    TO_CHAR(ENTERED_DATE,'DD-MON-YY') AS ENTERED_DATE,
    CUST_NUM,
    NULL,
    NULL,
    Null,
    NULL,
    NULL,
    NVL(CANCELLED_QUANTITY,'0') AS CANCELLED_QUANTITY,
  NULL,
  NULL,
  NULL,
  parent_line_number,
    chip_set_flag,
     NULL,
    NULL,
    Null,
    NULL,
    NULL,
    WAREHOUSE
    FROM ORDER_DETAIL where parent_line_number in (select Id from query1) ;
  </code></pre>
  
  <p>For example, if I have data like:</p>
  
  <pre><code>id  column1  cloumn2 ..... parent_line_number
  
  1   abc      anj           null
  2   fff      eee           null
  3   bbb      www           1
  4   abh      nnn           1
  5   amm      ff            2
  6   aaa      ss            3
  7   sss      fff           null
  </code></pre>
  
  <p>then if my <code>query1</code> returns IDs 1 and 7, then my final result would be:</p>
  
  <pre><code>id  column1  cloumn2 ..... parent_line_number
  
  1   abc      anj           null
  3   bbb      www           1
  4   abh      nnn           1
  7   sss      fff           null
  </code></pre>
  
  <p>It would be great if we can order in such a way that it shows the first ID followed by its child data, then the second ID and its child data, etc.</p>