× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.