October 11, 2007, 11:21 AM
spydj1Simple focexec query
Hi,
I have a flat file of data. looks something something like
a0001 1
a0002 2
a0003 3
a0001 2
a0002 3
a0003 1
All the 1's in col 10 , 2's col 11, 3's col 12
I want to run a focexec that produces
a0001 1 2
a0002 2 3
a0003 1 3
All fields defined SUFFIX=FIX in their relevant cols
i.e.
FILENAME=RACFGRP SUFFIX=FIX
SEGNAME=ROOT
FIELDNAME=USER, ALIAS=user, FORMAT=A8,
FIELDNAME=blk, ALIAS=b1, FORMAT=A1,
FIELDNAME=racf1, ALIAS=rac1, FORMAT=A1,
FIELDNAME=RACF2, ALIAS=rac2, FORMAT=A1,
FIELDNAME=RACF3, ALIAS=rac3, FORMAT=A1,
I cannot seem to get away from Focus producing:
a0001 1
2
a0002 2
3
a00003 1
3
Any help would be appreciated.
Thanks
October 11, 2007, 11:41 AM
Tony AI take it you mean that your data looks like this? -
a0001 1
a0002 2
a0003 3
a0001 2
a0002 3
a0003 1
Have you tried?
TABLE FILE RACFGRP
SUM MAX.RACF1
MAX.RACF2
MAX.RACF3
BY USER
END
T
October 11, 2007, 12:19 PM
GinnyJakesBuilding on Tony's example:
TABLE FILE RACFGRP
SUM MAX.RACF1
MAX.RACF2
MAX.RACF3
BY USER
ON TABLE HOLD FORMAT ALPHA
END
DEFINE FILE HOLD
DRACF1/A1=IF RACF1 EQ ' ' THEN RACF2 ELSE RACF1;
DRACF2/A1=IF RACF2 EQ RACF1 THEN RACF3 ELSE IF RACF2 EQ ' ' THEN RACF3 ELSE RACF2;
DRACF3/A1=IF RACF3 EQ RACF2 THEN ' ' ELSE IF RACF3 EQ ' ' THEN RACF3;
END
TABLE FILE HOLD
PRINT DRACF1 AS RACF1
DRACF2 AS RACF2
DRACF3 AS RACF3
BY USER
END
You might have to play with that 3rd DEFINE if one user had all 3.
Also, your master needs a little work:
FILENAME=RACFGRP,SUFFIX=FIX
SEGNAME=ROOT
FIELDNAME=USER, ALIAS=user, FORMAT=A5,ACTUAL=A5,$
FIELDNAME=blk, ALIAS=b1, FORMAT=A1,ACTUAL=A1,$
FIELDNAME=RACF1, ALIAS=rac1, FORMAT=A1,ACTUAL=A1,$
FIELDNAME=RACF2, ALIAS=rac2, FORMAT=A1,ACTUAL=A1,$
FIELDNAME=RACF3, ALIAS=rac3, FORMAT=A1,ACTUAL=A1,$
Don't forget the actuals. There was also a comma missing on the first line and some field names were in lower case.
October 11, 2007, 12:44 PM
spydj1quote:
TABLE FILE RACFGRP
SUM MAX.RACF1
MAX.RACF2
MAX.RACF3
BY USER
END
Many thanks. Thats done the trick.