Where are you seeing that %VALUES() is a valid OPNQRYF function?

What is the logical expression that you want to execute? That is, describe the logic and we’ll see how to express it for OPNQRYF.

Tom

Looks like you are after a string
(DQB8ST *EQ “A” *AND DQJRCE *EQ [value in &Market] ) *OR (DQB8ST = “D” *AND DQZRCD = “A” *AND DQETCD = “01″)

And to get that would look like something this…
CHGVAR VAR(&QRYSLT) VALUE(‘(DQB8ST *EQ “A” *AND +
DQJRCE *EQ ‘ *CAT &MARKET +
*CAT ‘ ) *OR (DQB8ST = “D” *AND DQZRCD = +
“A” *AND DQETCD = “01″)’)

Phil

Actually there is one more thingy
If you’re using the double quote key ” — I’m not sure that works in OpnQry
Alternative is two single quotes ” — CL reads two single quotes as insert a quote here

which would make the final string read as
(DQB8ST *EQ ‘A’ *AND DQJRCE *EQ [value in &Market] ) *OR (DQB8ST = ‘D’ *AND DQZRCD = ‘A’ *AND DQETCD = ’01′)

Phil

It appears that your variables are character fields. If this is true, they need to be surrounded by the double quote character, not two single quotes.
Here is an example from a working program at our shop. The double upright characters are the same as *CAT. It is hard to see here but the second line has a double quote before the single quote preceding the first double upright. Following the second double upright there is a single quote followed by a double quote.

The result is ‘*and TALOC#=”WHS” *and . . .’

CHGVAR VAR(&QRYSEL) VALUE(‘TACMP# =’ |> &CMP# |> +
‘*and TALOC# = “‘ || &LOC# || ‘” *and +
TACNTD = “0″ *and TAFZDT =’ || &FRZDAT |> +
&SELSPECIE)

This is an example from a working program on our system. This prticular one uses to single quotes rather than one double quote. I have other programs that use double quotes so I guess you can use whichever you want.

In this one I have created the select statment in a variable. That way, if I get an error, I can see what data is contained in the variables &FROMDATE and &THRUDATE which are passed into this program.

CHGVAR     VAR(&QRYSELECT) VALUE('(YSDATE *GE' *BCAT +
             &FROMDATE *BCAT '*AND YSDATE *LE' *BCAT +
             &THRUDATE *BCAT '*AND YTYPE *EQ ''RS'' + 
             *AND YSHIP# *EQ %VALUES(''165828'' +     
             ''165925'' ''1658HL'' ''1659HL'' +       
             ''1658MS'' ''1659NS'' ''165825''))')     

OVRDBF     FILE(MA#SLHS23) TOFILE(MA#SLHS23) +    
             OVRSCOPE(*JOB) SHARE(*YES)           
                                                  
OPNQRYF    FILE((MA#SLHS23)) QRYSLT(&QRYSELECT) + 
             KEYFLD(*FILE) OPNSCOPE(*JOB)         

@Teandy

I agree with the sample you have given… But I need to use a variable with the %values function. That is the point I am getting an error.

Don’t understand the %VALUES thingy.
The way I’ve written it the value of &MARKET is substituted for the field &MARKET as the string (&QRYSLT is built.
Does &MARKET contain one value that sould be compared to DQJRCE ?
And incidently. Is DQJRCE numeric or character.
Phil

Okay, I see where %VALUES() is defined under Restricted built-in functions.

Due to the way it works, if you wanted a set of values such as (”165828” ”165925” ”1658HL” ”1659HL” ”1658MS” ”1659NS” ”165825”) to be used as a %VALUES() list, then you have two choices.

The first choice is to have as many variables as the maximum number of values. Each of those variables would be populated with a value and each variable would be listed in the %VALUES() function.

The other choice is to have a single large variable that holds all of the values with at least one space between all of them.

Further, when it all gets concatenated together, the final result must have the quote marks in place around each value. The quote marks can apparently be either double-quotes or doubled single-quotes. You can have the quotes inside the list variable, or add them from the outside as long as there is space to insert them into.

For example, if there are three different MARKET values, you could conceivably have something like:

chgvar  &MARKET  ( '"M01" "M02" "M03"' )

It’s hard to tell how that’s going to appear when this editor gets done with what I typed, but it should show that &MARKET is getting assigned a string of characters consisting of double-quote marks, letters and digits. It should appear as if there is one market code M01, a second one M02 and a third M03. All three have double-quotes around them and are separated by blanks.

Once &MARKET has the appropriate string of characters, you can then concatenate the &MARKET variable into the string that holds the %VALUES() function.

The entire QRYSLT() parameter is just an arbitrarily long string of characters that you generate. All you do is build that string so that it makes sense. For a list of values, you have to ensure that each value is quoted.

Do you have a couple examples that should be in the &MARKET list?

Tom

So then it looks something like…

Does %VALUES() actually need to be upper-cased? And the *CAT and *TCAT should give the same result in this usage.

The most important element we’re missing is that we don’t know what is actually in &MARKET. And after that, we’d probably need to know the data type of DQJRCE. If it’s numeric, then I’d assume that there’d be no need for quotes inside of &MARKET.

Tom

Tonmasked about the case of %Values. It can be upper or lower. Here is an example from a working program:

CHGVAR VAR(&QRYSEL) VALUE(‘cmp#oa =’ *BCAT &CMP# +
*BCAT ‘*and loccoa =”‘ *CAT &LOCC *CAT ‘” +
*and ortcoa = “S” *and ocpcoa *ne “V” +
*and dshpoa =’ *BCAT &DATE7 *BCAT ‘*and +
pfscoa = %values(“PPM” “DEL” “DL$”)’)

I have not tried using %values as coded in the question. To the best of my knowledge, %values only accepts multiple values which will be tested against the field which preceeds the %values statement. You can also use “not” to exclude a set of values from the results.