|
View previous topic :: View next topic
|
| Author |
Message |
srj1957
New User
Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA
|
|
|
|
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  |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Did you look at the data in HEX?
What happens if you try to print it? |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
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 |
|
|
srj1957
New User
Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
srj1957
New User
Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA
|
|
|
|
| I'm using SPUFI with native SQL to spufi output dsn |
|
| Back to top |
|
|
srj1957
New User
Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA
|
|
|
|
| I just want to be able to write multiple output on seperate lines from the SQL statement... Can this be done ? |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
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 |
|
|
srj1957
New User
Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA
|
|
|
|
| Thx..that UNION method works well |
|
| Back to top |
|
|
jerryte
Active User
Joined: 29 Oct 2010
Posts: 202
Location: Toronto, ON, Canada
|
|
|
|
| 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 |
|
|
|
|
