View previous topic :: View next topic
|
Author |
Message |
thunderstorm
New User
Joined: 23 Mar 2007 Posts: 35 Location: pune
|
|
|
|
Hi,
I have the following requirement, Please let me know whether this can be achieved by SORT?
File Length - 19 both input and output
TYPE - PS
Field desc.
EMPNO - X(5).
EMPNAME - X(5).
ACTIVE - S9(9) comp.
DEPARTMENTNO - 9(5).
For same EMPNO there could be multiple rows with same data but possibility of having different active status.
For ACTIVE value could be 1 or 0.
Example:
Code: |
11111ABCDE 100002
11111ABCDE 000003
11111ABCDE 000004
11111ABCDE 000002
11111ABCDE 000002
21111BBCDE 000002
21111BBCDE 000003
21111BBCDE 100004
21111BBCDE 000002
71111CBCDE 000002
71111CBCDE 000003
71111CBCDE 000004
41111DBCDE 000002
41111DBCDE 100003
41111DBCDE 000004
|
Output file:
In output file I need all records for the same empno only if at least one record for that employee is having active state as 1 (at position 14).
From file above emp no - 11111,21111,41111 should get written to the output file. For easy understanding I have grouped this file by EMP NO.
But in reality it might not be.
Code: |
11111ABCDE 100002
11111ABCDE 000003
11111ABCDE 000004
11111ABCDE 000002
11111ABCDE 000002
21111BBCDE 000002
21111BBCDE 000003
21111BBCDE 100004
21111BBCDE 000002
41111DBCDE 000002
41111DBCDE 100003
41111DBCDE 000004
|
Will this be achievable by single sort step?
I search the forum not able to get any relevant discussion.
If anything is there already which I missed to spot, please let me know I will check that link.
Thanks in advance. |
|
Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007 Posts: 1399 Location: IL, USA
|
|
|
|
Hi thunderstorm,
do you want sequence of records withing key fields to be mentained in output also?
i see you are in output you shown but just want to confirm as solution would differ a bit. |
|
Back to top |
|
|
thunderstorm
New User
Joined: 23 Mar 2007 Posts: 35 Location: pune
|
|
|
|
Hi Escapa,
I need the output sorted by first 5 bytes (Empno).
Please let me know incase you need any other information. |
|
Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
|
|
|
|
It is a simple job.
1. Use the same file as input for both JNF1 and JNF2
2. Code the join key fields as 1,5,A for both files and code the REFORMAT as (F1:1,19)
3. Code a JNF2CNTL to include only (11,4,BI,EQ,1) and if you think you will have more than 1 record per key with an active status of 1 then code SUM FIELDS=NONE |
|
Back to top |
|
|
thunderstorm
New User
Joined: 23 Mar 2007 Posts: 35 Location: pune
|
|
|
|
Hi Skolusu,
I am not in front of the system to test your JCL. But from seeing it, I guess it will extract only the records with with Binary 1 @ position 11-14.
But my requirement is to get all the records for the same empno, if at least one record has 1 @ position 11-14.
And this involves only one file.
Please pardon me if my understanding is wrong.
Anyways I will test this solution once I back @ my desk (Monday). |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
And this involves only one file. |
Suggest you completely read (and re-read) suggestions that come from DFSORT development . . .
Quote: |
1. Use the same file as input for both JNF1 and JNF2 |
Wait until you are back on the system, run a test, and then post your challenge if the given code does not do what you want (rather than what you requested).
A big part of your understanding this suggestion is understanding that a file may be JOINed to itself. . . |
|
Back to top |
|
|
thunderstorm
New User
Joined: 23 Mar 2007 Posts: 35 Location: pune
|
|
|
|
Dick - You are right, I should have waited till I got back to my system
Skolusu - You solution works absolutely fine... Thanks a lot for your valuable time. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Quote: |
I should have waited till I got back to my system |
Not to worry - i suspect it will be remembered in the future
Good to hear it is working - thank you for the followup
d |
|
Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007 Posts: 1399 Location: IL, USA
|
|
|
|
thunderstorm,
if possible post the solution you used. It would help many others who hit this page,, |
|
Back to top |
|
|
|