| View previous topic :: View next topic |
| Author |
Message |
vikramdr
Beginner
Joined: 15 Feb 2003
Posts: 15
Topics: 11
|
 Posted: Thu Jun 07, 2007 10:52 pm Post subject: Sort card needed for the below query |
 |
|
Hi,
I have a flat file which looks likes the below. | Code: |
Accnt# Service Option Qty
1234 001 001 1
1234 001 002 2
2345 001 001 1
3456 001 001 1
3456 001 002 2
3456 001 003 3
|
Case 1. Output file should have all the record which has more than one option to it. So it should be like this. | Code: |
1234 001 001 1
1234 001 002 2
3456 001 001 1
3456 001 002 2
3456 001 003 3 |
And it should produce a new recod which would have number of unique accounts in the newly created output file.
Case 2. Output file should have accounts which has qty > 1. The file should also have record which tell total number of records in the generated file.
Case3. I have two files but with different data. Key to both the file is Accnt#. Is there any way to get all the records that are in file 1 as well as in file2 using DFSORT/ICETOOL . If so please provide the sort card.
Thanks in advance.
Vikram.
_________________
Vikram |
|
| Back to top |
|
 |
krisprems
Beginner
Joined: 13 Dec 2006
Posts: 101
Topics: 4
Location: india
|
| Posted: Fri Jun 08, 2007 1:35 am Post subject: |
|
|
vikramdr
Solution for your 1st request:
| Code: | //*******************************************************
//GETMATCH EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD *
1234 001 001 1
1234 001 002 2
2345 001 001 1
3456 001 001 1
3456 001 002 2
3456 001 003 3
/*
//OUT DD DSN=&&SORT1,DISP=(MOD,PASS),SPACE=(TRK,(5,5)),UNIT=SYSDA
//TOOLIN DD *
SELECT FROM(IN) TO(OUT) ON(1,4,CH) HIGHER(1)
/*
//*
//STEP2 EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SORTIN DD DSN=&&SORT1,DISP=(MOD,DELETE,DELETE)
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
OUTFIL REMOVECC,
TRAILER1=(COUNT=(M11,LENGTH=8))
/*
|
Sortout contains:
| Code: | 1234 001 001 1
1234 001 002 2
3456 001 001 1
3456 001 002 2
3456 001 003 3
00000005
|
_________________
cHEERs
krisprems |
|
| Back to top |
|
|
krisprems
Beginner
Joined: 13 Dec 2006
Posts: 101
Topics: 4
Location: india
|
| Posted: Fri Jun 08, 2007 1:41 am Post subject: |
|
|
vikramdr
Just confirm if the 2nd case takes the i/p from the case 1 or not?
_________________
cHEERs
krisprems |
|
| Back to top |
|
|
krisprems
Beginner
Joined: 13 Dec 2006
Posts: 101
Topics: 4
Location: india
|
|
| Back to top |
|
|
vikramdr
Beginner
Joined: 15 Feb 2003
Posts: 15
Topics: 11
|
| Posted: Fri Jun 08, 2007 2:01 am Post subject: |
|
|
Input for case 2 is also the input to case1.
_________________
Vikram |
|
| Back to top |
|
|
krisprems
Beginner
Joined: 13 Dec 2006
Posts: 101
Topics: 4
Location: india
|
| Posted: Fri Jun 08, 2007 2:29 am Post subject: |
|
|
vikirum
For the 1st case is the record count correct ?
Or should it be 3?
For Case 2:
| Code: | //STEP1 EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//SORTIN DD *
1234 001 001 1
1234 001 002 2
2345 001 001 1
3456 001 001 1
3456 001 002 2
3456 001 003 3
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
INCLUDE COND=(14,1,CH,GT,C'1')
OUTFIL REMOVECC,
TRAILER1=(COUNT=(M11,LENGTH=8))
/*
|
Sortout contains | Code: | 1234 001 002 2
3456 001 002 2
3456 001 003 3
00000003
|
_________________
cHEERs
krisprems |
|
| Back to top |
|
|
Phantom
Data Mgmt Moderator
Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
|
| Posted: Fri Jun 08, 2007 9:32 am Post subject: |
|
|
krisperms,
| Quote: |
For the 1st case is the record count correct ?
Or should it be 3?
|
OP wanted to have a count of unique accounts. So, it should be 3 and not 5.
Thanks,
Phantom |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Fri Jun 08, 2007 1:53 pm Post subject: |
|
|
vikramdr,
You already got the solutions for case#2 and case #3. here is the solution for case #1
Case:1
| Code: |
//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD *
1234 001 001 1
1234 001 002 2
2345 001 001 1
3456 001 001 1
3456 001 002 2
3456 001 003 3
//OUT DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(IN) TO(OUT) ON(1,4,CH) ALLDUPS USING(CTL1)
//CTL1CNTL DD *
OUTREC IFTHEN=(WHEN=INIT,
OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,4))),
IFTHEN=(WHEN=(81,8,ZD,GT,1),
OVERLAY=(81:8C'0'))
OUTFIL IFOUTLEN=80,REMOVECC,
TRAILER1=(TOT=(81,8,ZD,M11,LENGTH=8))
/*
|
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
