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
 

 

get spaces in output dataset

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

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India

PostPosted: Mon Jan 30, 2012 9:25 pm    Post subject: get spaces in output dataset
Reply with quote

Hi,

I am running a SQL query to pull 10 character field into a 80 LRECL dataset. It is populating 10 characters perfectly but filling rest of 70 length with low-values. I am using SORT to get rid of those low-values. Can I achieve it with SQL itself to fill rest of 70 length with spaces instead of low-values?

Please help.

Thanks.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Jan 30, 2012 9:38 pm    Post subject:
Reply with quote

well, until you tell us exactly what you are doing,
we can not offer you any solutions,
and you are stuck with your sort solution - which should not be necessary.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Mon Jan 30, 2012 9:40 pm    Post subject:
Reply with quote

What language is your query embedded in? That program is IMHO the proper place to edit the output record, not SQL or *SORT.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Tue Jan 31, 2012 11:59 am    Post subject:
Reply with quote

Hi Ramsri...

Modify this query according to your needs...

Code:

SELECT SUBSTR(CHAR('COLUMN WHICH YOU WANT TO UNLOAD')||CHAR(' ',80),1,80)
FROM SYSIBM.SYSDUMMY1
WITH UR;
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jan 31, 2012 1:18 pm    Post subject:
Reply with quote

If you absolutely have no use for the remaining 70 bytes then why dont you set the record length as 10
Back to top
View user's profile Send private message
ramsri

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India

PostPosted: Tue Jan 31, 2012 6:07 pm    Post subject: Reply to: get spaces in output dataset
Reply with quote

gylbharat, my field does not reside on SYSIBM.SYSDUMMY1 table but in a different table.

I am asked to use a 80 byte dataset but just (low-values) should not appear after first 10 bytes is the requirement.

Thanks.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Jan 31, 2012 6:14 pm    Post subject: Re: Reply to: get spaces in output dataset
Reply with quote

ramsri wrote:
gylbharat, my field does not reside on SYSIBM.SYSDUMMY1 table but in a different table.
.



It would be really difficult to change the table name to the correct one.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Jan 31, 2012 6:30 pm    Post subject:
Reply with quote

ramsri,

if this a SPUFI select?, is this an unload? is this imbedded SQL?

until you tell us which process you are using to generate the output,
you will get nothing but silly answers, as the above post prove.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jan 31, 2012 7:48 pm    Post subject:
Reply with quote

gylbharat wrote:
Hi Ramsri...

Modify this query according to your needs...

Code:

SELECT SUBSTR(CHAR('COLUMN WHICH YOU WANT TO UNLOAD')||CHAR(' ',80),1,80)
FROM SYSIBM.SYSDUMMY1
WITH UR;


GYLBHARAT

Code:

SELECT CHAR(COLUMN NAME,80) FROM TABLE


should be a better I guess the above function doesnt work for version less than 8

Experts please correct me if am wrong
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Tue Jan 31, 2012 7:55 pm    Post subject: Re: Reply to: get spaces in output dataset
Reply with quote

gylbharat wrote:
Hi Ramsri...
Modify this query according to your needs...

ramsri wrote:
gylbharat, my field does not reside on SYSIBM.SYSDUMMY1 table but in a different table.

Ramsri, you have one guess as to which part need modification!
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Feb 01, 2012 1:17 pm    Post subject: Re: Reply to: get spaces in output dataset
Reply with quote

ramsri wrote:
gylbharat, my field does not reside on SYSIBM.SYSDUMMY1 table but in a different table.

I am asked to use a 80 byte dataset but just (low-values) should not appear after first 10 bytes is the requirement.

Thanks.


Hi...

You just need to change the table name and the column name...
Back to top
View user's profile Send private message
ramsri

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India

PostPosted: Thu Feb 09, 2012 12:06 am    Post subject: Thanks a ton to gylbharat
Reply with quote

Hi gylbharat,

I have used the solution you've suggested and got the expected results. Can you please explain how it works.....

Thanks.
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 FTP - JCL failed while passing FTP co... Suneetha1612 JCL & VSAM 12 Wed Nov 16, 2016 7:33 pm
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts Syncsort - Hard coded Value in output PORYES SYNCSORT 1 Thu Nov 03, 2016 9:23 am
No new posts output data in CSV Format janmejay COBOL Programming 8 Sat Oct 15, 2016 2:20 pm
This topic is locked: you cannot edit posts or make replies. output data in CSV Format janmejay DFSORT/ICETOOL 3 Sat Oct 15, 2016 2:16 pm


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