Re: CPYF and *NULL .....

On 3/22/11 5:56 AM, Greg Wenzloff wrote:

I had a CPYF statement in a CLP that compiled without error but
got a syntax error when it ran:

CPYF FROMFILE(BLRATEP) TOFILE(BECKTFR/BLRATEP) +
MBROPT(*ADD) INCREL((*IF BLSDT *GT 0) +
(*AND BLWHS *NE *BLANKS))

The error said that value of the second test use not valid.

CPF2843 "Value in INCREL expression number 2 not valid." would occur only because the field BLWHS is defined with fewer that seven characters. The origin for the error is that the literal comparison value is '*BLANKS' rather than a /special value/ representing an equivalent string of [space] blank characters. Had the field length for the character field BLWHS been at least 7-bytes, the data would have copied for all rows WHERE BLWHS<>'*BLANKS' which probably would have copied all rows... and presumably an undesirable outcome, so the error message was a bonus :-)

I looked into the reference manual and found that *BLANKS was not
addressed but that *NULL was.

F4=Prompt on the "Value . ." element of the INCREL parameter would also show only the special value *NULL being available beyond whatever CHARACTER type could be entered.

I switched out *BLANKS for *NULL and recompiled the program.

Now I'm thinking this decision was wrong and I should just use a
string like " ".
To me *null is a hex 00.
Blanks are hex 40.

The *NULL special value in that context refers to "the database NULL value" not the often described "null character" or its variant the "null string".

Any suggestions on how to phrase BLWHS *NE *BLANKS?

Use the literal ' ' [apostrophe space apostrophe] for the value to compare. Per the help text for the "Value" element, element 4:

Element 4: Value

*NULL
*NULL can be used as the value to test whether the field value
in a record is or is not null. Only the operators *EQ and *NE
are allowed if *NULL is specified. A "*EQ *NULL" relation is
true only if a field value in a record is null. A "*NE *NULL"
relationship is true only if a field value in a record is not
null.

character-value
Specify the value (up to 256 characters) to be compared with the
contents of the specified field. The specified value cannot be
another field name. The field value must be specified in
apostrophes if it contains blanks or special characters, and it
may be specified in hexadecimal format. Any non-*NULL
comparison to a field value in a record that is null will test
false, regardless of the operator used. <<SNIP>>

<<SNIP>>

Regards, Chuck