Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to create a Line break in DB2 SQL ?

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

New User


Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA

PostPosted: Fri Jun 14, 2013 6:55 pm    Post subject: How to create a Line break in DB2 SQL ?
Reply with quote

I'm using DB2 SQL v8 on OS390 and I'm wanting to create a multiple line output using single SQL statement

The output I want to look like this...
e.g.
THIS IS LINE 1
THIS IN LINE 2
THIS IS THE LAST LINE

I'm trying X'0D' to force a new line/ line break as suggested after googling for a solution
Code:
SELECT
' THIS IS LINE 1 ' || X'0D' || 'THIS IS LINE 2' || X'0D' || 'THIS IS THE LAST LINE'
FROM DB2.TABLE
FETCH FIRST 5 ROWS ONLY;

the output when viewed in a text editor look like this
Code:
THIS IS LINE 1 THIS IS LINE2 THIS IS THE LAST LINE

Any help would be greatly appreciated icon_question.gif
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jun 14, 2013 7:21 pm    Post subject:
Reply with quote

Hello,

Did you look at the data in HEX?

What happens if you try to print it?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Fri Jun 14, 2013 8:46 pm    Post subject:
Reply with quote

Remember that z/OS does not use end of line markers - it uses LRECL and for VB files the control info stored at the front of each line.

Why not jsut pass you data back to the program and have it do the work leaving DB2 to do what it is supposed to do - retrieve/store data. The more 'extra' work you give DB2 to do the more you impact usage for ALL DB2 users whereas doing it in your program you are only impacting yourself.
Back to top
View user's profile Send private message
srj1957

New User


Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA

PostPosted: Fri Jun 14, 2013 8:48 pm    Post subject:
Reply with quote

Hello,

The data looks like this
Code:
 THIS IS LINE 1  THIS IS LINE 2 THIS IS THE LAST LINE
4ECCE4CE4DCDC4F44ECCE4CE4DCDC4F4ECCE4CE4ECC4DCEE4DCDC4
038920920395501003892092039550203892092038503123039550


So the X'0D' is not the correct solution as it just creates blank.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jun 14, 2013 9:04 pm    Post subject:
Reply with quote

Hello,

Is the posted data from a DISPLAY or actually looking at the file in an editor?

Where is the printout routed (mainframe printer or pc printer)?

Is the SQL embedded in some code or is it run in SPUFI or ?
Back to top
View user's profile Send private message
srj1957

New User


Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA

PostPosted: Fri Jun 14, 2013 9:08 pm    Post subject:
Reply with quote

I'm using SPUFI with native SQL to spufi output dsn
Back to top
View user's profile Send private message
srj1957

New User


Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA

PostPosted: Fri Jun 14, 2013 10:02 pm    Post subject:
Reply with quote

I just want to be able to write multiple output on seperate lines from the SQL statement... Can this be done ?
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 642
Location: Whitby, ON, Canada

PostPosted: Sat Jun 15, 2013 12:04 am    Post subject:
Reply with quote

One way to do it is to split the query up. The first query uses a SUBSTR function to retrieve "THIS IS LINE 1", the next one retrieves "THIS IS LINE TWO" and the last retrieves "THIS IS THE LAST LINE" UNION the queries together and the results will appear on three consecutive lines.

It's ugly, but this approach can work. (I left out a lot of details, deliberately).
Back to top
View user's profile Send private message
srj1957

New User


Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA

PostPosted: Mon Jun 17, 2013 5:29 pm    Post subject: Reply to: How to create a Line break in DB2 SQL ?
Reply with quote

Thx..that UNION method works well
Back to top
View user's profile Send private message
jerryte

Active User


Joined: 29 Oct 2010
Posts: 183
Location: Toronto, ON, Canada

PostPosted: Thu Jun 20, 2013 2:49 am    Post subject:
Reply with quote

Try using X'0D25' to separate the lines. This is the EBCDIC equivalent of a CRLF When the file is FTP from mainframe to PC then it will be converted into separate lines.
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
This topic is locked: you cannot edit posts or make replies. Rexx to create GDG by scanning JJ result krish.deepu CLIST & REXX 5 Tue Oct 25, 2016 5:32 pm
No new posts How can we create a flat file in JAVA... rakesh.v18 Java & MQSeries 7 Fri Sep 23, 2016 10:46 pm
No new posts Insert Lines in JCL with Rexx after a... Willy Jensen CLIST & REXX 3 Tue Aug 30, 2016 4:18 pm
No new posts Need to create file with Dynamic Name... kapil27 JCL & VSAM 4 Wed May 25, 2016 9:45 am
No new posts How to create/edit/update a new GDG V... karthikbalakumar8 JCL & VSAM 2 Fri Feb 05, 2016 6:35 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us