Union of query with another query based on the first

Question

I want a union of one query with joins and another query which uses the output of first query as input.

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.

My first query is:

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

If I call the above query query1, then I want all the data returned by query1 plus I want to fetch all the data from table order_detail where parent_line_number is the ID from query1.

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) ;

For example, if I have data like:

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

then if my query1 returns IDs 1 and 7, then my final result would be:

id  column1  cloumn2 ..... parent_line_number

1   abc      anj           null
3   bbb      www           1
4   abh      nnn           1
7   sss      fff           null

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.

Answer 1

Try an approach with WITH clause:

WITH first_query AS(
  SELECT .... first query goes here
  ...
  ...
)
SELECT ........
  .... second query goes here ....
  .....
WHERE parent_line_number in (select Id from first_query)
UNION ALL
SELECT * FROM first_query

Answer 2

Expanding Kordiko's answer a bit to use your simplified example data:

with query1 as (
  select id, column1, column2, parent_line_number
  from order_detail
  where id in (1, 7)
)
select id, column1, column2, parent_line_number
from  query1
union all
select o.id, o.column1, o.column2, o.parent_line_number
from  query1 q1
join order_detail o on o.parent_line_number = q1.id;

        ID COL COL                      PARENT_LINE_NUMBER
---------- --- --- ---------------------------------------
         1 abc anj                                        
         7 sss fff                                        
         3 bbb www                                       1
         4 abh nnn                                       1

If you want to order the way you describe you can put that in a subquery (or another CTE) with dummy columns to hold the values you want to order by - the ID or parent ID, and a flag for whether the row is a parent or child - and use them for the ordering:

with query1 as (
  select id, column1, column2, parent_line_number
  from order_detail
  where id in (1, 7)
)
select id, column1, column2, parent_line_number
from (
  select id, column1, column2, parent_line_number, id as ord1, 0 as ord2
  from  query1
  union all
  select o.id, o.column1, o.column2, o.parent_line_number, q1.id as ord1, 1 as ord2
  from  query1 q1
  join order_detail o on o.parent_line_number = q1.id
)
order by ord1, ord2, id;

        ID COL COL                      PARENT_LINE_NUMBER
---------- --- --- ---------------------------------------
         1 abc anj                                        
         3 bbb www                                       1
         4 abh nnn                                       1
         7 sss fff