A CPYF Idiosyncrasy Revisited
August 28, 2002 Timothy Prickett Morgan
I just noticed the article regarding the CPYF idiosyncrasy in January 23 issue of Midrange Guru, OS/400 Edition . The writer wanted to copy records with a user profile field that was equal to CEM.
I had the same problem. To solve it, I defined two variables. The first contained the exact value for which I was looking. I used the Change Variable (CHGVAR) command to set the second variable to the value of the first variable with a ‘!’ character added to the end of text. I used the Copy File (CPYF) command with the Include records by field test (INCREL) parameter with an equal condition on the first parameter and a less-than condition on the second:
PGM PARM(&UN1) DCL VAR(&UN1) TYPE(*CHAR) LEN( 9) DCL VAR(&UN2) TYPE(*CHAR) LEN(10) CHGVAR VAR(&UN2) VALUE(&UN1 *TCAT '!') CPYF FROMFILE(CEMFILE) TOFILE(QTEMP/X) + MBROPT(*REPLACE) CRTFILE(*YES) + INCREL(( *IF PRFNAM *EQ &UN1) + (*AND PRFNAM *LT &UN2))
This technique works well with standard alphanumeric fields, but care is needed where fields contain values less than the hexadecimal equivalent of ‘!’.
— Richard J. Barnett
Thanks for the tip, Richard. Instead of using the exclamation point, you could use x’41’, which is the character just above a blank:
PGM PARM(&UN1) DCL VAR(&UN1) TYPE(*CHAR) LEN( 9) DCL VAR(&UN2) TYPE(*CHAR) LEN(10) DCL VAR(&HEX41) TYPE(*CHAR) LEN( 1) + VALUE(X'41') CHGVAR VAR(&UN2) VALUE(&UN1 *TCAT &HEX41) CPYF FROMFILE(CEMFILE) TOFILE(QTEMP/X) + MBROPT(*REPLACE) CRTFILE(*YES) + INCREL(( *IF PRFNAM *EQ &UN1) + (*AND PRFNAM *LT &UN2))
I like this technique, but be aware that it won’t work if there are embedded blanks. For example, this code will retrieve records with a user profile value of CEM, and omit records with user profiles such as CEMA and CEMT, but it will include a record with a user profile value of CEM X, where there is a blank between the M and the X.
ADVANCED SYSTEMS CONCEPTS
SEQUEL meets all your iSeries and AS/400 data access needs in a single, integrated solution:
Take 6 minutes to view a SEQUEL ViewPoint ScreenCam movie to see how simple Windows-based AS/400 and iSeries data access can be! In just a few short minutes, you can find out ways to make your job easier and improve data access throughout your organization. Download the ViewPoint movie here .
For more information or a FREE trial of SEQUEL, call 847/605-1311 or visit Advanced Systems Concepts.