Re: CPYF and *NULL .....

[CRPence <CRPbottle@xxxxxxxxx>]

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