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 column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Split 1 file into 10 output Files - S... Prasanth Kumar SYNCSORT 5 Sat Sep 16, 2017 12:02 am
No new posts Remove leading spaces from numeric field rexx77 SYNCSORT 6 Wed Sep 06, 2017 2:15 am
No new posts JES2 job size field matching Windows ... SRICOBSAS All Other Mainframe Topics 4 Tue Sep 05, 2017 5:49 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm

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