Split one field into many rows

liangl · New User Joined: 23 Mar 2008 Posts: 13 Location: china

has any way split one column(datatype is longvar) into many rows by SQL only?

My requirement is to get proc or query statement from QMF table(Q.OBJECT_DATA) line by line, just like we read proc in QMF proc panel.

if I use query like this:

SELECT APPLDATA
FROM Q.OBJECT_DATA
WHERE
NAME='procname'

result:
-----------------------------
run query q.abc save data as q.data1 run query q.def save data as q.data2
-----------------------------

then I just get proc statement in one row or 2 rows.

How can I get proc statement line by line such as each line only have 79 chars by SQL?
I want result like this:
----------------------
run query q.abc
save data as q.data1
run query q.def
save data as q.data2
----------------------

the table structure is below

Column Name Col No Col Type Length
* * * *
------------------ ------ -------- ------
OWNER 1 VARCHAR 128
NAME 2 VARCHAR 128
TYPE 3 CHAR 8
SEQ 4 SMALLINT 2
APPLDATA 5 LONGVAR 3774

Bharath Bhat · Posted: Thu Nov 13, 2008 3:32 pm

You can use SUBSTR function on the column you want. This can get you 79 characters, but does not satisfy your requirement. It can be coded as Dynamic embedded SQL inside a program to get what you need.

liangl · New User Joined: 23 Mar 2008 Posts: 13 Location: china

yes, I know I can use SUBSTR, but the question is I have to use SUBSTR for 47 times, because APPLDATA have 3774 length. Another thing is I don't want to use Dynamic embedded SQL inside a program, so only static SQL statement in QMF. is it still possible to do that?