| Split Data separated by comma ; then create a collection with data mapped from other columns [message #596582] |
Wed, 25 September 2013 01:20  |
 |
abhenav123
Messages: 6
Registered: July 2013
|
Junior Member |
|
|
DB Used : Oracle 10g.
A table X : NUM, INST are column names
NUM ----- INST
1234 ----- 23,22,21,78
2235 ----- 20,7,2,1
1298 ----- 23,22,21,65,98
9087 ----- 20,7,2,1
-- PROBLEM 1 :
-- Based upon requirement :
1) Split values from "INST" Column : suppose 23
2) Find all values from "NUM" column for above splitted value i.e 23 ,
Eg:
For Inst : 23 ,
It's corresponding "NUM" values are : 1234,1298
3) Save these values into
A table Y : INST, NUM are column names.
INST NUM
23 1234,1298
-- PROBLEM 2 :
1) I have a thousand records in Table X ,
and for all of those records i need to split and save data into Table Y.
Hence, I need to do this task with best possible performance.
2) After this whenever a new data comes in Table X,
above 'split & save' operation should automatically be called and append corresponding data wherever possible ...
My sincere thanks in advance,
Every suggestion accepted, Please provide a solution to this.
|
|
|
|
|
|
|
|
| Re: Split Data separated by comma ; then create a collection with data mapped from other columns [message #596596 is a reply to message #596586] |
Wed, 25 September 2013 02:26  |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Not entirely true. You are propagating the issue, just moving it to a different table/column. Based on your requirements, you are looking to split out the comma separated list in 'INST', That's great, exactly what you should be doing, but you then want to create a comma separated list of values for each instance of 'INST'. This is a terrible idea and, as Michel already said, goes against normalization. Whilst the structure of the data coming into you is not in your control, what you do with that data afterwards IS within your control.
Anyway, a google search will turn up many techniques for doing the first part:
Here's the search I performed.
|
|
|
|
| Re: Split Data separated by comma ; then create a collection with data mapped from other columns [message #596651 is a reply to message #596596] |
Wed, 25 September 2013 07:45 |
|
abhenav123
Messages: 6
Registered: July 2013
|
Junior Member |
|
|
Thanks for reply ...
I am using this approach to solve this issue :
1) Made a splitter function (using instr, substr functions) : This function will split all the values from "INST" column and store into a collection : "collection_1" which is being returned to calling procedure.
2) Then a procedure is made which loops like :
For Eg :
p_rowid : Collection containing rowid's of all the rows from the table X
collection_1 : Collection containing splitted values from one row (INST) at a time.
collection_2 : Collection containing splitted values from next row (INST) at a time.
save_inst : Collection containing each "inst" value;
save_num : Collection containing respective "rowid" value for each "inst" in save_inst.
--------------------------------------------------------------------------------------
Select rowid into p_rowid from X;
collection_1 = split_function(...); -- returns splitted value for a 1st row
for i in 1..collection_1.count -- iterates through values of collection_1
loop
collection_2 = split_function(...); -- returns splitted value for next row.
for j in (i+1)..collection_2.count --iterates through values of collection_2
loop
if ( collection_1(i) = collection_2(j)) then
save_inst(i) := collection_1(i);
save_num (i) := p_rowid(i);
exit;
end if;
end loop;
end loop;
-------------------------------------------------------------------------------
Pardon any mistakes in above code ... (just an overview)
Actually i am able to get Unique "INST" values in one collection : [save_inst]
and Their ONE-TO-ONE "NUM" value in another collection : [save_num].
-- But I have to append each num value using ',' by getting it through rowid value.
-- Secondly, after doing all this operation once, I want a trigger to append any new num value that falls into same inst value using ','
[ For doing so, Should I use UPDATE to put new value of num in table Y or is there any other way out ? ]
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
