split a contents of a field of each record and store in another table

[ssmvijay]

Hai everybody...!
I have a problem here .. Hope I can get help by answers..

I have a table A
ID DATA
1 aa;bb
2 aa;bb;cc
3 aa;cc
4 bb;cc

I wish to get a table B like

ID A B C
1 aa bb null
2 aa bb cc
3 aa null cc
4 null bb cc

I would like get table B a result by using DB2 UDF,,
wish have details of create function & how to call it ..
I am new to DB2.. waiting for Help..!

Thanks in advance...

[r035198x]

You'd have to write your own string split function. Luckily someone has already done that and explained the process here:http://www.ibm.com/developerworks/da...03stolze1.html

[ssmvijay]

Thanks for your response..
I have tried that one earlier.. it throws [Error] - &;WHERE ordinal. SQLCODE=-7, SQLSTATE=42601, DRIVER=3.53.71

I am using DB2 Aqua Data Studio 8.0.8

so waiting for further clarification..
It would be very helpful ..
Thanks once again in advance..

[r035198x]

What code did you execute when you go that error?

[ssmvijay]

Here is the code I used...

1)CREATE or REPLACE FUNCTION elemIdx (string CLOB(64K) ) 
2)RETURNS TABLE ( ordinal INTEGER, index INTEGER ) 
3)LANGUAGE SQL 
4)DETERMINISTIC 
5)NO EXTERNAL ACTION 
6)CONTAINS SQL 
7)RETURN WITH t(ordinal, index) AS 
8) ( VALUES ( 0, 0 ) 
9)  UNION ALL 
10) SELECT ordinal+1, COALESCE(NULLIF(LOCATE(';', 
11) string,index+1),0),LENGTH(string)+1) FROM t           
12) WHERE  ordinal < 10000 AND LOCATE(';', string, 
13)     index+1) <> 0 ) 
14)     SELECT ordinal, index  FROM   t 
15)     UNION ALL       
16)     SELECT MAX(ordinal)+1, LENGTH(string)+1 FROM   t 

In the above code I am not clear about the following...

[ CLOB(64K) ]
[ <10000 AND ]
[ <> ]

Thanks in advance..

[r035198x]

Don't just copy and paste code without looking at what you are executing. That is quite a dangerous thing to do.

1.) Don't execute it with those line numbers
2.) Read the tutorial to understand what the SQL is doing.

[ssmvijay]

Thanks..
I do executed without line numbers ..
just for understanding i added line number manually for posting reply...
as i mentioned early
[ CLOB(64K) ]
[ <10000 AND ]
[ <> ]

are not mentioned clearly in tutorial...
Thanks again..

[r035198x]

Most likely some of the characters are supposed to be less than (<) instead of &lt; and their code formatter didn't display them right so replace &lt; with < and &gt; with >

[ssmvijay]

yes you are right...!

http://www.ibm.com/developerworks/da...03stolze1.html

helps to get the seperate Stings from a group of String as input ...

i.e.s from 'aa;bb;cc' as input gives 'aa' 'bb' 'cc' in three records as output...

but still i am not getting how to get ...

table B
ID A B C
1 aa bb null
2 aa bb cc
3 aa null cc
4 null bb cc

as output from the input

table A
ID DATA
1 aa;bb
2 aa;bb;cc
3 aa;cc
4 bb;cc

i.e., how to pass each DATA in all records of table A as String (by looping) as input
& how to insert the result into the fields A B C of table B respectively..

Thanks Again in Advance...

[r035198x]

insert from a select (or from a group of selects)

[ssmvijay]

Thanks.. but still not getting how to insert with group of select...
I have the following...
[code - function returns a table]

CREATE or REPLACE FUNCTION elemIdx ( string VARCHAR(225) ) 
   RETURNS TABLE ( ordinal INTEGER, index INTEGER ) 
   LANGUAGE SQL 
   DETERMINISTIC 
   NO EXTERNAL ACTION 
   CONTAINS SQL 
   RETURN 
      WITH t(ordinal, index) AS 
         ( VALUES ( 0, 0 ) 
           UNION ALL 
           SELECT ordinal+1, COALESCE(NULLIF(LOCATE(';', string, index+1), 0),LENGTH(string)+1) FROM   t 
                  WHERE  ordinal<1000 AND  LOCATE(';', string, index+1) <> 0 ) 
      SELECT ordinal, index FROM   t 
      UNION ALL 
      SELECT MAX(ordinal)+1, LENGTH(string)+1 FROM t 
 
 

[code - query ]

SELECT * 
FROM TABLE (elemIdx('abc; def; ghi; 123') ) AS t ORDER BY 1
 

[result of above query - a table]

  ordinal  index
    0       0
    1       4
    2       9
    3       14
    4       19

[result]

[code-function returns a table]

CREATE OR REPLACE FUNCTION elements ( string VARCHAR(225) ) 
   RETURNS TABLE ( elements VARCHAR(225) ) 
   LANGUAGE SQL 
   DETERMINISTIC 
   NO EXTERNAL ACTION 
   CONTAINS SQL 
   RETURN 
      WITH t(ordinal, index) AS 
         ( SELECT ordinal, index FROM   TABLE ( elemIdx(string) ) AS x ) 
      SELECT SUBSTR(string, t1.index+1, t2.index - t1.index - 1) 
            FROM   t AS t1 JOIN t AS t2 ON ( t2.ordinal = t1.ordinal+1 ) 
 
 

[code- query that returns a table again]

SELECT VARCHAR(elem, 100) 
FROM   TABLE ( elements('abc; def; ghi; 123') ) AS t(elem)
 
 

[result of above query - a table]

  1
 abc
 def
 ghi
 123
 

[result]

i donot have any clear idea about
how to proceed further....

Help me how to proceed ... Thanks in Advance...

[r035198x]

insert into your new table values(colA, colB)
values
(select from the table with the concatenated values and split the values and get the first split record, select from the table with the concatenated values and split the values and get the second split record)

[ssmvijay]

Thanks...
I am not getting any progress...
as I am getting close to deadline,could you help me with queries using above functions..

source table is
Table_name as source
ID DATA
1 aa;bb
2 aa;bb;cc
3 aa;cc
4 bb;cc

and destination table is

Table_name as destination (ID is auto generated)
ID A B C
- - - -

expected result is
Table_name: destination
ID A B C
1 aa bb null
2 aa bb cc
3 aa null cc
4 null bb cc

Thanks in Advance..