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 Splitting one row into multiple Rows ... Rajan Moorthy DFSORT/ICETOOL 9 Mon May 14, 2018 10:58 pm
No new posts How to include a particular field val... sakrat DFSORT/ICETOOL 8 Fri Apr 06, 2018 8:27 pm
No new posts Extract the records with a PD field's... sudhakar84 DFSORT/ICETOOL 11 Mon Apr 02, 2018 7:26 pm
No new posts Searching a table for the field name? socker_dad COBOL Programming 8 Sat Mar 31, 2018 2:57 am
No new posts help to replace columns by rows Vikas Maharnawar DFSORT/ICETOOL 8 Tue Mar 06, 2018 3:27 pm

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