Solved

Split values of data column in Oracle db table

Posted on 2013-06-25
7
1,498 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)
Comment Utility
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
Comment Utility
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)
Comment Utility
>> (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 31

Accepted Solution

by:
awking00 earned 500 total points
Comment Utility
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]
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now