|
View previous topic :: View next topic
|
| Author |
Message |
Thagun
New User
Joined: 25 Jul 2008
Posts: 15
Location: Chennai
|
|
|
|
Hi,
I am extracting the data from table into a flat file. This process is successful and i am getting the following output,
| Code: |
1000ABCDEF25/11/2008
2000ABCDEF25/11/2008
3000ABCDEF25/11/2008
4000ABCDEF25/11/2008 |
Can we manipulate the same step to give an formatted output like,
EMP NO NAME DATE
| Code: |
1000 ABCDEF 25/11/2008
2000 ABCDEF 25/11/2008
3000 ABCDEF 25/11/2008
4000 ABCDEF 25/11/2008 |
by changing the select SQL?
Edited: Please use BBcode when You post some code, that's rather readable...Anuj |
|
| Back to top |
|
 |
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Code: |
| select col1 || ' ' || col2 || ' ' ..... |
May be you can give this a try. But I think you'll have to use CHAR(..) if the target column is not character. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
Hi Thagun,
Your code is been edited to show the proper spacing - I was under the impression that you want a single sapce between the columns when you unload them but "BBcoded" output shows a rather different spacing - please confirm - is that what you need else post your expected output again. Please use BBcode when you post and preview it before posting so that the actual spacing, which you need, remains intact. |
|
| Back to top |
|
|
Thagun
New User
Joined: 25 Jul 2008
Posts: 15
Location: Chennai
|
|
|
|
Hi,
Thanks for your reply. The BBcode formating done by Anuj is how i want the output to look like. Since i am not familiar with BBcode not able to post the same.
Can you let me know how to get the output in the BBcode format provided by Anuj.
Thanks,
Thagun |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello Thagun and welcome to the forum,
At the top of the reply panel are the bbTags - one of which is the Code tag.
One easy way to use the code tag is to copy/paste jcl, code, data, or whatever you want to preserve alignment into your post. Then (without dong any editing) highlight tis info and click Code.
Use Preview to see how your post will appear to the forum and when you are satisfied with how your post looks, sUBMIT. |
|
| Back to top |
|
|
Thagun
New User
Joined: 25 Jul 2008
Posts: 15
Location: Chennai
|
|
|
|
Hi D.sch,
Thanks for your tips on posting in BBcode format.
While doing an unload from Table to file I am getting the output format(in my original post) and but i want the result of unload to look like this; Can we manipulate the control card?
| Code: |
Emp Name Emp No Date
ABCDEF 123456 11/27/2008
ABCDEF 123456 11/27/2008
ABCDEF 123456 11/27/2008
ABCDEF 123456 11/27/2008
|
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
It should help us help you if you post the query being used for the unload. |
|
| Back to top |
|
|
Thagun
New User
Joined: 25 Jul 2008
Posts: 15
Location: Chennai
|
|
|
|
Hi,
This is the query used in my unload job.
| Code: |
//SYSTSIN DD *
DSN SYSTEM(DB2B)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARM('SQL')
/*
//SYSIN DD *
SELECT EMP_NO,EMP_NAME,EMP_JOIN_DT FROM EMPLOYEE
WITH UR;
/*
//SYSPRINT DD SYSOUT=*
|
|
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
Hi,
What do you want can be "visible" if you open the "unloaded" file with the respective copy-book OR you might try running the query in SPUFI. |
|
| Back to top |
|
|
Thagun
New User
Joined: 25 Jul 2008
Posts: 15
Location: Chennai
|
|
|
|
Hi,
It will be viewed outside the mainframe so using copybook or SQL option is ruled out. I am planning to format the unload file using a SYNCSORT. Can you let me know how it can be done. I have a working JCL to do it using ICEMAN but its not allowed.
Can you help in SYNCSORT. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Quote: |
| SQL option is ruled out |
I m sorry,I m not getting this point. What's wrong in formatting this in SQL itself? |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
Hi,
You are opening your cards very slowly..
| Quote: |
| It will be viewed outside the mainframe |
In what "way" it will be viewd ? Another allication, note pad form, excel..
| Quote: |
| I have a working JCL to do it using ICEMAN but its not allowed |
ICEMAN invokes the SORT installed at your shop, it's just another way - change ICEMAN to SORT, job should work fine. |
|
| Back to top |
|
|
Aaru
Senior Member
Joined: 03 Jul 2007
Posts: 1287
Location: Chennai, India
|
|
|
|
Thagun,
| Quote: |
| Can you help in SYNCSORT |
Yep, this can be done. You can make INREC to reformat the fields. You will have to use X for adding spaces. I do not have access to mainframes.
Please go for the above method if the SQL thng is not working.
| Quote: |
| I have a working JCL to do it using ICEMAN but its not allowed |
Not allowed to use JCL or Is the system not allowing? |
|
| Back to top |
|
|
Thagun
New User
Joined: 25 Jul 2008
Posts: 15
Location: Chennai
|
|
|
|
Hi,
Apologies for the confusion and Thanks to everyone for the tips. I used SYNCSORT instead of ICEMAN and the JCL is working fine formatting.
Regards,
Thagun |
|
| Back to top |
|
|
Aaru
Senior Member
Joined: 03 Jul 2007
Posts: 1287
Location: Chennai, India
|
|
|
|
Thagun,
| Quote: |
| Apologies for the confusion and Thanks to everyone for the tips. |
No worries and you are welcome.
| Quote: |
| I used SYNCSORT instead of ICEMAN and the JCL is working fine formatting |
Ideally it should not make any difference. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Thagun,
I personally believe it would have been better if you'd done the formatting in SQL in this case. Why would you need an additional pass of data in sort? |
|
| Back to top |
|
|
|
|
