OPNQRY File

135 pts.
Tags:
AS/400
CLP
OPNQRYF
RPG
value
OPNQRYF Query
Hi, I am having difficulty in figuring out the syntax for using the special keyword %values with the variables in the OPNQRYF command. Below is the &QRYSLT I used.
 CHGVAR VAR(&QRYSLT) VALUE('(DQB8ST *EQ "A" *AND +
DQJRCE *EQ %VALUES( ' *TCAT &MARKET +
*CAT ' )) *OR (DQB8ST = "D" *AND DQZRCD = +
"A" *AND DQETCD = "01")')
And I am getting an error on this. PLs help.


Software/Hardware used:
AS400

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hey,

I finally got what I wanted —

while creating my &QRYSLT
I divided the Query in 2 part and then concatenated it. And Voila.. it works…

CHGVAR VAR(&QRYSLT) VALUE(‘(DQB8ST *EQ “A” *AND +
DQJRCE *EQ %VALUES( ‘ *TCAT &MARKET +
*CAT ‘ )) *OR (DQB8ST = “D” *AND DQZRCD = +
“A” *AND DQETCD = “01″)’)

CHGVAR VAR(&QRYSLT1) VALUE(‘(DQB8ST *EQ “A” *AND +
DQJRCE *EQ %VALUES( ‘)

&QRYSLT1 = (” *TCAT &MARKET +
*CAT ‘ )) *OR (DQB8ST = “D” *AND DQZRCD = +
“A” *AND DQETCD = “01″)’)

Din’t know if initially ot was just a qoute misplaced … But now it works..

Thank for your help all…

Discuss This Question: 13  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • philpl1jb
    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
    49,950 pointsBadges:
    report
  • philpl1jb
    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
    49,950 pointsBadges:
    report
  • WoodEngineer
    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)
    6,680 pointsBadges:
    report
  • Teandy
    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)         
    
    5,860 pointsBadges:
    report
  • Ankita
    @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.
    135 pointsBadges:
    report
  • philpl1jb
    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
    49,950 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • philpl1jb
    Somtimes we actually do some programming. So then it looks something like this? CHGVAR VAR(&QRYSLT) VALUE(’(DQB8ST *EQ “A” *AND + DQJRCE *EQ %Values(‘ *CAT &MARKET + *CAT ‘)) *OR (DQB8ST = “D” *AND DQZRCD = + “A” *AND DQETCD = “01″)’) Phil
    49,950 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • WoodEngineer
    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.
    6,680 pointsBadges:
    report
  • Ankita
    [...] Ankita discovered that if you try some times, you just might get what you need: They were tackling some basic AS/400 programming and a number of community members helped them [...]
    0 pointsBadges:
    report
  • Ankita
    [...] Ankita has a problem with a OPNQRY File, but community input on syntax, style and keywords helped solve [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following