Unpack a column in a view 1

[MikeAJ]

I need to write a view that joins part numbers in one table to a wom_id in another. The problem is the part numbers are crammed into a single column and delimited by "|". Is it possible to write a view on top of this join, that will normalize the result?

Here is my query:

Select w.wom_id, c.common_config_part_nos
  from wom w, global_content c
 Where c.global_content_id = w.global_content_id
   and c.common_config_part_nos IS NOT NULL;

Here is the result:

wom_id      common_config_part_nos
973         777317-2010|777318-120|777318-110|777318-301|777318-401|777318-200|778617-04|778618-01|778805-90
1095        777318-423|777317-2120|778617-04|778618-01|778805-90

I'd like to create a view that looks like this:

wom_id      common_config_part_nos
973         777317-2010
973         777318-120
973         777318-110
973         777318-401
973         777318-200

Is this possible?

Thanks,
Mike

 

[SantaMufasa]

Mike,

Before I post a solution for you, could you please advise what is the maximum number of "common_config_part_nos" that one might ever expect for a single wom_id?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

Is this a new design or an existing application. This design is screaming out for a redesign and the addition of some child tables.

Bill
Oracle DBA/Developer
New York State, USA

 

[MikeAJ]

Thank you for the replies! This is a legacy system, and a redesign is not in scope right now. So I have to make due with the bad design.

Dave, The most elements I found in the column was 20.

Thanks,
Mike

 

[SantaMufasa]

Mike,

Since I do not have your tables with which to test this out (syntactically), please create the following VIEW, run the SELECT to test the view, then post the success or failure back here:

create or replace view wom_view as
select wom_id
      ,substr(pn
          ,instr(pn,'|',1,rn)+1
          -- above locates start pos of current part_no (pn)
          ,(instr(pn,'|',1,rn+1)-instr(pn,'|',1,rn))-1
          -- above calculates length of current pn
             ) part_number
  from (Select w.wom_id, '|'||c.common_config_part_nos||'|' pn
          from wom w, global_content c
         Where c.global_content_id = w.global_content_id
           and c.common_config_part_nos IS NOT NULL)
      ,(select rownum rn from all_objects where rownum <= 30)
 where rn <= (length(pn) - length(replace(pn,'|',null)))-1
/

Test SELECT:

select * from wom_view
 order by wom_id;

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[MikeAJ]

Dave, you nailed it!! Thank you so much for your help!!!

Mike