Retrieving list of elements from a Large Memo field 1

[barnard90]

I have an Employee table which has a field called
" Employee_list "

The structure is
Deptno Deptname Employee_List

This field has all the employee ids working a specific department stored in it separated by commas .
The data in Employee_list field is like 101 , 102, 103, 104 ...etc


The data in the current table is

Deptno Deptname Employee_List
100 Accounting 101,102,103,104
200 Sales 201,202,203,204

I would like to separate the employees from the Employee_List field and report them as individual employee ids in a separate view


The structure of the new view is

Deptno Deptname Emp_id
100 Accounting 101
100 Accounting 102
100 Accounting 103

How do I do that ? Please suggest

 

[SantaMufasa]

Barnard,

First, I hope you know that whoever created your EMPLOYEE_LIST data badly broke E.F. Codd's Rule of "First-Normal Form"...The table should have the structure that you are requesting in your new view. What are the chances of changing your original table to look like the VIEW for which you are asking?

To untangle your data from its knickers will be an absolute mess. I'll think about a VIEW that will give you what you want and then reply.

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

 

[SantaMufasa]

Here is a query (which you can turn into a VIEW) that gives you the results you want:

select deptno
      ,deptname
      ,trim(
         substr(','||employee_list||','
             ,instr(','||employee_list||',',',',1,rn)+1
              -- above line locates start point for each emp_id
             ,instr(','||employee_list||',',',',1,rn+1)
              -- above line locates emp_id end delimiter
             -(instr(','||employee_list||',',',',1,rn)+1)
              -- above line calculates length of emp_id
               )
           ) emp_id
  from barnard, (select rownum rn
                   from all_objects
                  where rownum <= 50)
       -- above in-line query allows for up to 50 employees
 where rn <=
       length(translate(','||employee_list||',','A0123456789 ','A'))-1
       -- above translation makes 1 row per employee
order by 1,2,3;

DEPTNO DEPTNAME             EMP_ID
------ -------------------- ------
   100 Accounting           101
   100 Accounting           102
   100 Accounting           103
   100 Accounting           104
   200 Sales                201
   200 Sales                202
   200 Sales                203
   200 Sales                204

Let us know how you like it.

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

 

[barnard90]

Santa Mufasa

Thanks a lot for your valuable query