Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Split values of data column in Oracle db table

Posted on 2013-06-25
7
1,521 Views
Last Modified: 2013-06-26
Hi,
I am using Oracle 11g database.

I have two tables table1 with columns A ,B, Flag and table2 with columns C,D, flag
I have data in table1 as

A     B               flag
---    ---             ---
1    10,20          Y

ie coulumn B is having comma separated values (one or any number of values)

In table2 I have data as

C     D      flag
---   ----
1    20      Y
1    30     Y

Now I need to merge the data of table1 into table2 using matching of table1.A=table2.C and comma separated values of table 1.B with the values of table2.D column
and need to update the table2 data as

C    D   Flag
---  ---   -----
1   10  Y       --Newly inserted row
1   20 Y       -- Update other columns of this row
1   30 N      -- Update Flage to 'N' as 30 is not present in table1.B column for value 1 of column A


Can you please suggest how can I achieve this?

Thanks.
0
Comment
Question by:GouthamAnand
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39274508
Try this:

drop table tab1 purge;
create table tab1(a number, b varchar2(10), flag char(1));

insert into tab1 values(1,'10,20','Y');

drop table tab2 purge;
create table tab2(c number, d number, flag char(1));

insert into tab2 values(1,20,'Y');
insert into tab2 values(1,30,'Y');
commit;


select a,mynum, max(flag) flag
from
(
	select a, to_number(rtrim(regexp_substr(b,'([[:alnum:]]*)(,)?',1,column_value),',')) mynum, flag
	from tab1,
		table(
  	cast(
  	multiset(select level from dual connect by level <= ((length(b)-length(replace(b,','))))+1)
  	as sys.odcinumberlist
  	)
  	)
union
select c,d,'N' from tab2
)
group by a, mynum
/

Open in new window

0
 

Author Comment

by:GouthamAnand
ID: 39275258
Thank you very much. I will try this and let you know the result.

But I think this is "selecting" the values as per my requirement.

But I have to "Update" the table tab2 when column values of C and D match with A and B(after splitting the comma separated values). In my ex.  1 and 20 values.

I have some other columns in table1 and some other columns in table2. which need to be matched and "updated in table2".

ie I have other columns which needs to be updated along with flag column in table2 based on the values of table1.

Also I need to "Insert" in the table tab2 when they do not exits .(in my ex. they are 1 and 10 along with other coulumns).

Then again I need to update "only flag column" where the record value for column d does not exists in the comma separated values.

In my example its 1 and 30. ie I am doing "logical delete of that record". But not physically deleting.

As I mentioned in my question
C    D   Flag
---  ---   -----
1   10  Y       -- should  get inserted in table2
1   20 Y       -- should update the other columns of this row.As flag is anyway same.
1   30 N      -- sholud update Flag to 'N' .( as 30 is not present in table1.B column for value       1 of column A). Here I do not touch other columns. Because its logical delete.

Request you to suggest for the Update and Insert (kind of Merge statement) of the table2 as per the above requirement.

Thank you for your kind help in advance.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39275277
>> (kind of Merge statement)

Might I suggest the MERGE command?

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9016.htm#SQLRF01606

If you cannot figure it out, just post back and I'll see if I can mock up a test case.  MERGE is pretty straight forward.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 32

Accepted Solution

by:
awking00 earned 500 total points
ID: 39275581
I've been playing with the merge and the following appears to be working:
merge into tab2 t2
using
 (select t.a ,trim(x.column_value.extract('e/text()')) b, flag
 from tab1 t,
 table (xmlsequence(xmltype('<e><e>' || replace(t.b,',','</e><e>')||  '</e></e>').extract('e/e'))) x
 union all
 select tab2.c, to_char(tab2.d) d, 'N' flag
 from tab2
 where not exists
 (select 1 from tab1 where tab1.a = tab2.c and instr(tab1.b,tab2.d) > 0)
 ) t1
on (t1.a = t2.c and t1.b = t2.d)
when matched then
update set t2.flag = t1.flag
when not matched then
insert values
(t1.a, t1.b, t1.flag);
It doesn't take into account the other columns, but should be easily modified. Can you provide examples of the tables with the other columns (1 or 2 should suffice) with your expected result?
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39275961
I know you want to use the MERGE, but maybe this one helps, too ;-)

with sel as
 (select trim(regexp_substr(str, '[^ '']+', 1, level)) as val_col,
         a as id_col,
         flag
    from (select rownum as id,
                 trim(regexp_replace(a.b, ',+', ' ')) str,
                 a.a,
                 a.flag
            from tab1 a)
  connect by instr(str, ' ', 1, level - 1) > 0
         and id = prior id
         and prior dbms_random.value is not null)
select b.c id_col,
       to_char(b.d) val_col,
       'N' flag
  from tab2 b
 where not exists (select 'X'
          from sel
         where val_col = to_char(b.d))
union
select id_col,
       val_col,
       flag
  from sel;

Open in new window

0
 

Author Closing Comment

by:GouthamAnand
ID: 39276948
Thank you very much.

This is perfectly suites my requirement.

This addressed both the problems which I am facing while using  merge statement. ie
1) Splitting the values in table1 column.
2) Updating table2 values which are "not there in table1 split values."
( as normal merge update only matching values)

As you said I can easily modify the statement accordingly to add the other columns while update and insert statement of merge.

Thanks and Regards.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39278041
Glad to help. Just be sure to update the flag field to satisfy the requirement when the record doesn't exist in the other table along with the other columns, even though you will frequently be updating the flag from 'Y' to "Y' when it does.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question