How to write sort card?
Moderators: Frank Yaeger, DikDude, Moderator Group
-
- Member
- Posts: 4
- Joined: Wed Oct 08, 2014 1:58 pm
- Location: Mangalore
How to write sort card?
Hi all,
I am working on sort card for the first time, so I need help. I have to unload data from db2 and arrange it in format. I have written unload query but output is not in expected format. now I have to write sort step for that. Please help me in this.
Expected Output should be:
col1(length3) col2(length40)col3(length3)col4(padding char)col5(space) = total length of the file 50
001aaaaa 102byr
002abdddd 106byr
003dssdaaaaa 109byr
Current output:
col1(length3) col2(length40) col3(length5)col4(padding) = total length of the file is 51
1 aaaaa 102 byr
2 abdddd 106 byr
3 dssdaaaaa 109 byr
Can you please let me know how to arrange the current output file in expected form?
Thanks in advance
I am working on sort card for the first time, so I need help. I have to unload data from db2 and arrange it in format. I have written unload query but output is not in expected format. now I have to write sort step for that. Please help me in this.
Expected Output should be:
col1(length3) col2(length40)col3(length3)col4(padding char)col5(space) = total length of the file 50
001aaaaa 102byr
002abdddd 106byr
003dssdaaaaa 109byr
Current output:
col1(length3) col2(length40) col3(length5)col4(padding) = total length of the file is 51
1 aaaaa 102 byr
2 abdddd 106 byr
3 dssdaaaaa 109 byr
Can you please let me know how to arrange the current output file in expected form?
Thanks in advance
Regards,
Veena
Veena
-
- Active Member
- Posts: 732
- Joined: Thu May 24, 2012 4:07 am
Re: How to write sort card?
You need to use the Code tags when posting something which requires the preservation of spacing. Use the Preview button to see what the post will look like.
It would seem likely that you can get the output you want from the Unload. Can you show the control cards you are using for that. and someone may be able to assist.
It is of course possible to reformat the data with SORT. You will need to use BUILD, it looks like you want leading zeros on your first field. It is not clear, due to the formatting problems, what you need for any other reformatting.
It would seem likely that you can get the output you want from the Unload. Can you show the control cards you are using for that. and someone may be able to assist.
It is of course possible to reformat the data with SORT. You will need to use BUILD, it looks like you want leading zeros on your first field. It is not clear, due to the formatting problems, what you need for any other reformatting.
-
- Member
- Posts: 4
- Joined: Wed Oct 08, 2014 1:58 pm
- Location: Mangalore
I was able to resolve it and now am not getting how to add leading zero's.
Below is the query and sort card which I have written.
ID should be 3 digits only and in output it should be like '001, 002 ... 099'. So until '99' rows I have to do padding of zeroes. Can you please tell me how to do it?
Below is the query and sort card which I have written.
Code: Select all
Code:
SELECT CAST(ID AS CHAR(3))
,NAME
,CAST(MGR_ID AS CHAR(3))
FROM EMP
WHERE ID > 0
ORDER BY ID
WITH UR;
SORT FIELDS=COPY
OUTREC FIELDS=(1,3,4,40,44,3,47:C'BYR')
Output:
1 aaaaaa 101BYR
2 BBBBB 102BYR
3 CCCCC 103BYR
4 DDDDD 105BYR
Regards,
Veena
Veena
-
- Active Member
- Posts: 732
- Joined: Thu May 24, 2012 4:07 am
If you have lots of records, it is pointless making the change after you get to three digits. However, if the numbers are unique, you don't really have lots anyway, but....
The BYR will be done to each record, the conversion only when necessary. The 51:C'X' is just to show you the conversion working only when needed.
Code: Select all
OPTION COPY
INREC IFTHEN=(WHEN=INIT,
OVERLAY=(47:C'BYR')),
IFTHEN=(WHEN=(3,1,CH,EQ,C' '),
OVERLAY=(1,3,UFF,
TO=ZD,
LENGTH=3,
51:C'X'))
If you cast to CHAR it will produce '3' instead of 003 - standard rules across most languages. You need to cast to a numeric field type although I do ot know if any will retain leading zeros.
By the way, this is either a sort query or a DB2 query so why post in the JCL section of the forum when there are sections for both sort and DB2?
By the way, this is either a sort query or a DB2 query so why post in the JCL section of the forum when there are sections for both sort and DB2?
Regards
Nic
Nic
-
- Member
- Posts: 4
- Joined: Wed Oct 08, 2014 1:58 pm
- Location: Mangalore
Thanks, Collins Its working.
But I have one more question.
but I want zero padding in the 3rd column when MGR_ID is having 0. Can you please suggest me how to do it?
But I have one more question.
Code: Select all
input file:
0 aaaaa 106
1 bbbbb 103
2 cccccc 0
3 ddddd 102
102sfsfsf 209
103fbbfbf 0
105ggggg 0
SORT FIELDS=COPY
INREC IFTHEN=(WHEN=INIT,
OVERLAY=(47:C'BYR')),
IFTHEN=(WHEN=(3,1,CH,EQ,C' '),
OVERLAY=(1,3,UFF,
TO=ZD,
LENGTH=3,
51:C'X')),
IFTHEN=(WHEN=(44,1,CH,EQ,C'0'),
OVERLAY=(44:C'000'))
output file:
000aaaaa 106byr
001bbbbb 103byr
002cccccc 0 byr ---> for this the second if condition is not working
003ddddd 0 byr ---> for this the second if condition is not working
102sfsfsf 209byr
103fbbfbf 000byr
105ggggg 000byr
Regards,
Veena
Veena
-
- Active Member
- Posts: 732
- Joined: Thu May 24, 2012 4:07 am
IFTHEN processing stops when a condition is met. If you MGR is on a three-digit-id record, it will work (if you need to prove it to yourself, it is documented).
On your first contional IFHTEN, instead of )), to close it, use ),HIT=NEXT), which will cause it to continue looking for an IFTHEN with a condition.
On your first contional IFHTEN, instead of )), to close it, use ),HIT=NEXT), which will cause it to continue looking for an IFTHEN with a condition.
-
- Member
- Posts: 4
- Joined: Wed Oct 08, 2014 1:58 pm
- Location: Mangalore
Collins,
As you suggested, )HIT=NEXT), option is not working. Yes, MGR_ID is a 3 digit record field. Can you please suggest me how to solve this.
Can you please correct?
As you suggested, )HIT=NEXT), option is not working. Yes, MGR_ID is a 3 digit record field. Can you please suggest me how to solve this.
Code: Select all
INREC IFTHEN=(WHEN=INIT,
OVERLAY=(47:C'BYR')),
IFTHEN=(WHEN=(3,1,CH,EQ,C' '),
OVERLAY=(1,3,UFF,
TO=ZD,
LENGTH=3),HIT=NEXT),
IFTHEN=(WHEN=(3,44,CH,EQ,C' '),
OVERLAY=(44,3,UFF,
TO=ZD,
LENGTH=3,
51:C'X'))
Regards,
Veena
Veena
-
- Active Member
- Posts: 732
- Joined: Thu May 24, 2012 4:07 am
FREE TUTORIALS
Tutorials
Free tutorials from mainframegurukul
- JCL Tutorial
Covers all important JCL concepts. - Cobol Tutorial
This tutorials covers all Cobol Topics from STRING to COMP-3. - DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming. - SORT Tutorial
This Tutorial covers all important aspects of DFSORT with examples - CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page