Oracle SQL - Creating A Temporary Table Then Union To That Table

Question

I'm trying to run a query and store the results of the query in a temporary table then union a query onto the first that will join to that table.

My code so far looks like this-

 SELECT * FROM
  (WITH tmp2 AS
  (SELECT *
  FROM prod_trkg_tran ptt
  WHERE ptt.menu_optn_name = 'RF Split/Comb {Carton}'
  AND ptt.cntr_nbr         = '0031195609'
  )
  SELECT * FROM prod_trkg_tran
  )
UNION
SELECT *
FROM prod_trkg_tran ttp
INNER JOIN tmp2 -- This doesn't work
ON ttp.tran_nbr          = tmp2.tran_nbr
WHERE ttp.menu_optn_name = 'RF Split/Comb {Carton}'
AND ttp.cntr_nbr        <> '0031195609'

Sorry that definately could have been clearer - I'm trying to use the tran_nbr to find all records under that tran_nbr using only a cntr_nbr, there are other records using the tran_nbr but with different cntr_nbrs. The error I get from my code is that tmp2 doesn't exist.

Any help would be much appreciated :)

7's

Answer 1

Try this:

WITH tmp2 AS
  (SELECT *
  FROM prod_trkg_tran ptt
  WHERE ptt.menu_optn_name = 'RF Split/Comb {Carton}'
  AND ptt.cntr_nbr         = '0031195609'
  )
SELECT * FROM tmp2
UNION
SELECT *
FROM prod_trkg_tran ttp
INNER JOIN tmp2
ON ttp.tran_nbr = tmp2.tran_nbr
WHERE ttp.menu_optn_name = 'RF Split/Comb {Carton}'
AND ttp.cntr_nbr        <> '0031195609'