A CPYF Idiosyncrasy Revisited

August 28, 2002 Timothy Prickett Morgan

Hey, Ted:

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.

— Ted

Sponsored By ADVANCED SYSTEMS CONCEPTS

SEQUEL meets all your iSeries and AS/400 data access needs in a single, integrated solution: Windows, Web or host user interfaces Convert AS/400 data into PC file formats E-mail or FTP query results, reports and spool files Run-time prompted queries and reports for end users IF-THEN-ELSE logic in queries and reports Report, form and label formatting second to none Easily convert date fields, character-to-numeric, numeric-to-character and other data manipulation SORT or JOIN using a calculated field Quick summarization of data with Tabling function Run multiple SEQUEL requests as one with the SEQUEL Scripting function OLAP Business Intelligence at a fraction of the cost of comparable solutions 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.