OPNQRYF %VALUES 2

[Skittle]

I have an OPNQRYF selection string that uses %VALUES to limit selected records:-

MyField *EQ %VALUES("A" "B "C")

I am having trouble finding the syntax for the reverse operation. I expected the following to be valid but its rejected at runetime.

MyField *NE %VALUES("A" "B" "C")

Any idea how I exclude values in an OPNQRYF selection string?

Dazed and confused

 

[MdnghtPgmr]

Skittle,

Can you cut and paste the entire command? I've got a couple of people looking at this and we need to see your quote marks and all that.

Thanks,
MdnghtPgmr

 

[MdnghtPgmr]

Skittle,

I checked the manual and found:

%VALUES (allowed-value...)
%VALUES is used to identify a list of allowed values for a field or expression. %VALUES must be specified as the right side of a relation whose operator is equal. The allowed-value arguments must be character string or numeric literals, to match the type of the field or expression specified as the left side of the relation. For example, to select only records where the second character of field CHARFLD has a value that is one of the values 'A', 'E', 'I', 'O', or 'U', specify the following:
'%SST(charfld 2 1) =
%VALUES(''A'' ''E'' ''I'' ''O'' ''U'')'

(bolding is mine)

So I guess you have to do:
MyField *NE "A" *AND MyField *NE "B" *AND MyField *NE "C"

What a pain!

HTH,
MdnghtPgmr

 

[Skittle]

Ok thanks.
The OPNQRY looks like this.

OPNQRYF    FILE((DETAIL) (HDR1) (HDR2)) +        
             FORMAT(FORMAT1) QRYSLT(&SELECT) +         
             KEYFLD((H1CUST) (D1CENT) (D1YEAR) +     
             (D1ORDN) (D1STAT)) JFLD((1/D1COMP +     
             2/H1COMP *EQ) (1/D1ORDN 2/H1ORDN *EQ) + 
             (1/D1COMP 3/H2COMP *EQ) (1/D1ORDN +     
             3/H2ORDN *EQ))

I just know you are gonna point out a silly error here .... )
Where &SELECT =

D1CENT *EQ 20 *AND D1YEAR *GE 05 *AND D1YEAR *LE 
05 *AND H1COMP *EQ "62" *AND  D1STAT *NE %VALUES("
01" "05")  *AND D1SALA *NE %VALUES("A5" "A6" "A7" 
 )

The error I get is CPD3137

Use of %VALUES on QRYSLT parameter not valid.

The interesting bit on the F1 help over the error is:-

  [b]-- Specifying the %RANGE, %VALUES, or %WLDCRD built-in function somewhere 
other than the second operand of an *EQ operator.[/b]                           
  -- Specifying the %NULL built-in function somewhere other than the second 
operand of an *EQ or *NE operator.                                          
  -- Specifying a built-in function argument that is an expression, where   
only a field name or constant is allowed.

Dazed and confused

 

[Skittle]

I was afraid of that...

Amazing that there is not a way to to a 'not equal to values'. I've tried sticking *NOT in but is doesn't like it at run time very much either. Even with careful use of brackets, the runtime execution will not have it.

I spent ages looking through manuals but couldn't find anything specific.

frustrating!



Dazed and confused

 

[Mercury2]

That will work :

Qryslt( ' [COLOR=red yellow] *NOT [/color](MyField *EQ %VALUES("A" "B "C")) ' )

It is *NOT and not %NOT )

 

[Skittle]

Thanks dude

Dazed and confused