Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Split one field into many rows

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
liangl

New User


Joined: 23 Mar 2008
Posts: 13
Location: china

PostPosted: Thu Nov 13, 2008 2:58 pm    Post subject: Split one field into many rows
Reply with quote

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
Back to top
View user's profile Send private message

Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Thu Nov 13, 2008 3:32 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
liangl

New User


Joined: 23 Mar 2008
Posts: 13
Location: china

PostPosted: Thu Nov 13, 2008 5:01 pm    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 1 Wed Dec 06, 2017 1:50 am
No new posts Can we combine the rows with same key... V S Amarendra Reddy DB2 12 Fri Dec 01, 2017 10:29 pm
No new posts Split files upto certain limit based ... bubbu75 DFSORT/ICETOOL 5 Tue Nov 14, 2017 11:11 pm
This topic is locked: you cannot edit posts or make replies. Incrementing current time field by 30... arunsoods DFSORT/ICETOOL 11 Thu Oct 26, 2017 3:22 pm
No new posts sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us