On 12-Jul-2011 10:57 , Peter_Vidal@xxxxxxxx wrote:
We are in V5R4 and I do not know what I am doing wrong with the CPYF
command.
Background:
a) I have a work file called I700W1
b) I have a master file called ITEMBL
c) I have item number "21737" and warehouse id "N1"
d) I want to copy the record information for this items from the
ITEMBL file into the I700W1 work file.
e) In both files, the item number field (ITNBR) and the warehouse id
(HOUSE) have the same field names.
Based on the previous scenario, my CPYF command looks like this:
CPYF FROMFILE(*LIBL/ITEMBL) TOFILE(*LIBL/I700W1) MBROPT(*REPLACE)
INCREL((*IF ITNBR *EQ &P1ITNBR)
(*AND HOUSE *EQ &P1WHID ))
FMTOPT(*MAP *DROP)
The result of this command is bringing multiple items, all starting
with "21737" that exist on the warehouse "N1":
21737
217371
217371T
217372
217372T
217373
217374
217375
Even if I change the CPYF command to do this:
CPYF FROMFILE(*LIBL/ITEMBL) TOFILE(*LIBL/I700W1) MBROPT(*REPLACE)
INCREL((*IF ITNBR *GE &P1ITNBR) (*AND ITNBR *LE &P1ITNBR)
(*AND HOUSE *GE &P1WHID ) (*AND HOUSE *LE &P1WHID ))
FMTOPT(*MAP *DROP)
I do not have different results. Any ideas?
Assumption: ITNBR is defined as CHAR(7) [or larger].
The same "problem" would occur for a command-line invocation using
INCREL((*IF ITNBR *EQ '21737')). To get that request to function on the
command line, the literal value would be required to be padded to the
full length of the field; e.g.
INCREL((*IF ITNBR *EQ '21737 '))
As I recall, via the CLP and use of the CL variable, the data in the
field is only being compared up to the _trimmed length_ of data in the
variable. The length of the variable data is passed to the CPP from the
fourth element [i.e. Value] of INCREL, and that length is what the CPYF
utility compares of the data for the specified field name. Thus the
comparison becomes effectively [which should elucidate the lack of any
effect for the attempt to perform both the *GE and the *LE test]:
If %SST(ITNBR 1 %LEN(&P1ITNBR)) *EQ &P1ITNBR
The general resolution is to construct the command string and pass
the command string [with the apostrophes as delimiters and appropriate
padding included] to a command interpreter such as QCMDEXC. The string
can be formatted in hex notation; e.g. to avoid issues with the
delimiters embedded, for non-printable characters, or also IIRC for
numeric fields.
Another resolution sometimes available, but unfortunately for INCREL
is not an option per CPF2843, is to declare the CL variable as one byte
longer than the field, and always place a non-blank character as the
last byte. That technique requires that the last\extra byte is beyond
the length of the field, and that similar to the alluded effect of
%SST() on the field data, only the relevant portion\length of the
variable would be compared. I thought there was a KB item [an "IBM
Software Technical Document"] on this technique for at least one of the
"Value" elements on the various selection parameters of the CPYF, but I
could find none.
Another option available which can be somewhat ugly is to fill the
variable with data that includes something other than trailing blanks;
sometimes assumptions about the data must be made to effect the desired
results from the added selection to the (*IF ITNBR *EQ &P1ITNBR).
If the only possible suffix data were always all digits then append
the lowest digit using CHGVAR (&P1ITNBR_2) (&P1ITNBR *TCAT '0'), then
use also (*AND ITNBR *LT &P1ITNBR_2).
If as inferred from the given scenario upper-case alphabetic as
suffix is possible then CHGVAR (&P1ITNBR_2) (&P1ITNBR *TCAT 'A'), then
use also (*AND ITNBR *LT &P1ITNBR_2).
If any printable data [including spaces] can be a suffix, then more
generally, effect the same as implied by CHGVAR (&P1ITNBR_2) (&P1ITNBR
*TCAT x'41'), then use also (*AND ITNBR *LT &P1ITNBR_2).
FWiW, in this scenario and other similar, I would opt for OPNQRYF and
CPYFRMQRYF with the former CL request using a QRYSLT including the
expression 'ITNBR *EQ "' *cat &P1ITNBR *cat '"' which evaluates to
'ITNBR *EQ "21737 "'. That query will more likely benefit from an
index on the item number field; i.e. while the CPYF might have some
logic [for this case, as with some other specific cases] which is in
effect its own optimizer, there is an actual query optimizer in effect
for the OPNQRYF in the CQE [Query Classic Engine].
Regards, Chuck
midrange.com
