CODEWITHSUNDEEP
sqloracle-database
SMORF
SMORF

Reputation: 499

Oracle SQL combine 2 selects to one column

Sorry if this sounds vague ... I have searched for the answer for this but, am finding it hard to explain - therefore hard to search for.

I have a really simple script...

select pr1.polypart_no match_from, pr1.part_no match_to
from oes_polybox_replace pr1
where pr1.plant = 'W'
and pr1.part_no = 
      (select max(pr2.part_no) 
          from oes_polybox_replace pr2 
          where pr2.plant = 'W' 
          and pr2.polypart_no = 'YPOLYGREY')

...that shows in column 1, a part number and in column 2, a generic part that can be used in place of the part number in column 1.

enter image description here

My issues is I need to add the part in column 2 to column 1, i.e.

enter image description here

Is there a way a add 2 select's to one column?

Upvotes: 0

Views: 58

Answers (1)

Robert Dupuy
Robert Dupuy

Reputation: 857

Here is a union example

select pr1.polypart_no match_from
from oes_polybox_replace pr1
where pr1.plant = 'W'
and pr1.part_no = 
      (select max(pr2.part_no) 
          from oes_polybox_replace pr2 
          where pr2.plant = 'W' 
          and pr2.polypart_no = 'YPOLYGREY')
UNION
select pr1.part_no match_from
from oes_polybox_replace pr1
where pr1.plant = 'W'
and pr1.part_no = 
      (select max(pr2.part_no) 
          from oes_polybox_replace pr2 
          where pr2.plant = 'W' 
          and pr2.polypart_no = 'YPOLYGREY')

UNION only selects distinct values, UNION ALL does not.

You could wrap this up further if you need to re-order.

SELECT match_from from (
select pr1.polypart_no match_from
    from oes_polybox_replace pr1
    where pr1.plant = 'W'
    and pr1.part_no = 
          (select max(pr2.part_no) 
              from oes_polybox_replace pr2 
              where pr2.plant = 'W' 
              and pr2.polypart_no = 'YPOLYGREY')
    UNION
    select pr1.part_no match_from
    from oes_polybox_replace pr1
    where pr1.plant = 'W'
    and pr1.part_no = 
          (select max(pr2.part_no) 
              from oes_polybox_replace pr2 
              where pr2.plant = 'W' 
              and pr2.polypart_no = 'YPOLYGREY')) tab
order by match_from asc

Upvotes: 1

Related Questions