Need help with setvar for iSeries query manager using not in ()

300 pts.
Tags:
IBM iSeries
Query Management Query
Query Manager
V5R2
I have an issue with a Query Manager where I need help with the syntax for a list of entries in the Select not in ( ) portion.

I have a command that will call an RPGLE module which will call the Query Manager. I know that the quotes have to be supplied by the calling program (found a lot of info in the other Tech articles that helped with this). But I am having trouble with this one.

From the RPG, I have variables for the quote = Q and the info in the () for the select statement.



EVAL      Q   = ''''                     

IF        EXCLUDE <> *BLANKS            

EVAL      EXCLUDES = '('                

FOR       X = 1 TO 6                    

IF        EXC(X) <> ' '                 

EVAL      EXCLUDES = EXCLUDES + ',' +   

         Q + EXC(X) + Q                 

ENDIF                                   

ENDFOR                                  

EVAL      EXCLUDES = %TRIM(EXCLUDES) + ')

ENDIF    




Software/Hardware used:
iSeries V5R2, STRQMQRY

Answer Wiki

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

Discuss This Question: 15  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
  • VGP
    wow, it lost most of my info: Here is the rest: After running this… EXCLUDES = ('A','B','P') EVAL WkCommand = 'STRQMQRY ' + 'QMQRY(qry1) ' + 'OUTPUT(*OUTFILE) ' + 'OUTFILE(QTEMP/myfile) ' + 'SETVAR((Q ' + Q+Q+Q+Q +') ' + '(COMPANY ' + COMPANY + ') (FROMDATE ' + FROMDATE + ') (TODATE ' + TODATE + ') (EXCLUDE ' + EXCLUDES + '))' the above statement in debug is WKCOMMAND = STRQMQRY QMQRY(qry1) OUTPUT(*OUTFILE) OUTFILE(QTEMP/myfile) SETVAR((Q '''') (COMPANY 1 ) (FROMDATE 19400101) (TODATE 20391231) (EXCLUDES ('A','B','P'))) qry1 SELECT Fileinvno, fileinvdte, filecode, from filename where TRIM(filecmp) = &Q&COMPANY&Q AND fileinvdte >= &FROMDATE AND fileinvdte <= &TODATE AND filecode NOT IN &Q&EXCLUDES&Q This is the error that I get Character ',' not valid following string ''A' '. Error found on *N command. I try the command for strqmqry on a command line and get the same errors. STRQMQRY QMQRY(qry1) OUTPUT(*OUTFILE) OUTFILE(QTEMP/myfile) SETVAR((Q '''') (COMPANY 1 ) (FROMDATE 19400101) (TODATE 20391231) (EXCLUDES ('A', 'B', 'P'))) If I put a space after the , I get this error Character ',' not valid following string ''A' '. Character ',' not valid following string ''B' '. If I remove the , altogether I get List or expression not valid for parameter SETVAR. If I use (EXCLUDES '(A B P)')) Comparison of selection operands may never be equal. SAVE DATA operation completed successfully. The SQL runs but selects everything in the table and does not use the not in portion. What should that part look like? Thanks for any help.
    300 pointsBadges:
    report
  • TomLiotta
    I don't see why RPG would create parms to pass into QM query which takes the parms to construct a SQL statement to run. Why not just construct the SQL in RPG and skip the complexity? But that's secondary and doesn't address your problem. However, this parm:
    (EXCLUDES (’A',’B',’P'))
    ...probably needs to look this way:
    (EXCLUDES '('’A'','’B'','’P'')')
    I'll be surprised if this editor lets all of those quotes come across correctly. I'll try to describe them. Your parentheses need to be inside of quotes. CL will interpret parens as list element delimiters, but you want them to be actual parts of the substitution value. And since it will all be inside a quoted string, each embedded quote needs to be doubled. Let us know if this makes sense or not. It might need some detail changes. After you paste code into this editor, select the pasted text and click the {code} button above the input area. That helps preserve the exact spacing and indents. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    The weirder parts of the EXCLUDES value are all just sequences of two apostrophes. There are two before and after the A, the B and the P. Tom
    125,585 pointsBadges:
    report
  • VGP
    Tom, The reason for the RPG to call the Query manager is because that was the way my manager said to do it. Sorry. But I think it may be because the RPG would have level checks and maybe the Query manager will not and easier to handle if it did. Thanks for the suggestion too. I will try it.
    300 pointsBadges:
    report
  • TomLiotta
    ...it may be because the RPG would have level checks... If SQL SELECT provides the column list and the list doesn't change, there shouldn't be any level checks. If SQL is also used for any I/O, there shouldn't be any way for level checks to occur at all -- there won't be any F-specs to level check. But again, that's secondary to your main problem. If building EXCLUDES still doesn't work out, post the new code block. We'll get it. Tom
    125,585 pointsBadges:
    report
  • VGP
    Tom, Still is not working for me. Here is the end result of the command in the RPG. STRQMQRY QMQRY(qry1) OUTPUT(*OUTFILE) OUTFILE(QTEMP/myfile) SETVAR((Q '''') (COMPANY 1 ) (FROMDATE 19400101) (TODATE 20391231) (EXCLUDES '(''A'',''P'',''B'')')) My message Token A was not valid. Valid tokens: FOR WITH FETCH ORDER UNION OPTIMIZE. RUN QUERY command failed with SQLCODE -104. RUN QUERY command ended due to error. STRQMQRY command failed. I get the same just typing the command on the command line. Instead of this, Is there maybe another way to accomplish the same thing inside the QM? Thanks for the help.
    300 pointsBadges:
    report
  • TomLiotta
    AND filecode NOT IN &Q&EXCLUDES&Q
    Now you should use:
    AND filecode NOT IN &EXCLUDES
    The outer quotes won't be in the SQL statement. You want the result to look this way:
    AND filecode NOT IN (’A',’B',’P')
    No quotes outside of the parens. Do you have a variable number of codes? Or are there always three of them? Are there other codes that might be used in other runs of the program? Tom
    125,585 pointsBadges:
    report
  • carlosdl
    EXCLUDES ‘(”A”,”P”,”B”)’
    It could be the site's editor, but the filecodes seem to be enclosed in double quotes, but I guess a pair of single quotes (if the single quote is used as an escape character) should be used. I'm not going to use the code tool to see if the quotes show correctly without it: Instead of EXCLUDES ‘("A","P","B")’ this could be needed: EXCLUDES '(''A'',''P'',''B'')’
    70,220 pointsBadges:
    report
  • carlosdl
    Nope, they look the same, so maybe VGP is using the correct quotation marks and my last comment should be ignored.
    70,220 pointsBadges:
    report
  • VGP
    Do you have a variable number of codes? Or are there always three of them? Are there other codes that might be used in other runs of the program? yes, it is variable, up to 6 allowed. And from a selection of 18 different possible codes. yes they are all single quotes. Tom, thanks for the suggestion.... I removed the &q and it is now AND filecode NOT IN &EXCLUDES Here is the end result of the command in the RPG. STRQMQRY QMQRY(qry1) OUTPUT(*OUTFILE) OUTFILE(QTEMP/myfile) SETVAR((Q ””) (COMPANY 1 ) (FROMDATE 19400101) (TODATE 20391231) (EXCLUDES ‘(”A”,”P”,”B”)’)) The error message is Token ' ' was not valid. Valid tokens: WHEN RUN QUERY command failed with SQLCODE -104 RUN QUERY command ended due to error. STRQMQRY command failed.
    300 pointsBadges:
    report
  • TomLiotta
    I created a table named FILENAME with column names and attributes matching the QM query. This QM query worked:
    SELECT
    Fileinvno, fileinvdte, filecode  from filename where
    TRIM(filecmp) = &Q&COMPANY&Q
    AND fileinvdte >= &FROMDATE
    AND fileinvdte <= &TODATE
    AND filecode NOT IN &EXCLUDES
    Two differences from your pasted code -- no comma after FILECODE and no blank line after the WHERE token. The blank line might be irrelevant, but the comma was a definite syntax error. And since Carlosdl is also interested and I'm not confident in the editor either, here's a straight paste: SELECT Fileinvno, fileinvdte, filecode from filename where TRIM(filecmp) = &Q&COMPANY&Q AND fileinvdte >= &FROMDATE AND fileinvdte <= &TODATE AND filecode NOT IN &EXCLUDES The STRQMQRY ran with no errors. The command looked like:
    STRQMQRY QMQRY(TSTQM)
             OUTPUT(*OUTFILE)
             OUTFILE(QTEMP/MYFILE)
             SETVAR((Q '''')
                    (COMPANY 1)
                    (FROMDATE 19400101)
                    (TODATE 20391231)
                    (EXCLUDES '(''A'',''P'',''B'')'))
    Or in straight paste: STRQMQRY QMQRY(TSTQM) OUTPUT(*OUTFILE) OUTFILE(QTEMP/MYFILE) SETVAR((Q '''') (COMPANY 1) (FROMDATE 19400101) (TODATE 20391231) (EXCLUDES '(''A'',''P'',''B'')')) The overlooked comma might be the biggest problem. I'm not at all clear why you got the error text "Token ‘ ‘ was not valid. Valid tokens: WHEN". That doesn't make much sense to me. Tom
    125,585 pointsBadges:
    report
  • carlosdl
    Thanks Tom. "The overlooked comma might be the biggest problem. I’m not at all clear why you got the error text “Token ‘ ‘ was not valid. Valid tokens: WHEN”. That doesn’t make much sense to me." Yes, the error messages were misleading, and I think that was the reason why nobody noticed the extra comma which would definitely cause a syntax error. And, it seems that there is no way to post two consecutive apostrophes without they becoming a double quotation mark.
    70,220 pointsBadges:
    report
  • TomLiotta
    The only use of WHEN in a SELECT clause should be in a CASE structure, AFAIK. The extra comma would cause the parser to assume that "FROM" was intended to be a column name even if it's a reserved word. How that morphs into expecting WHEN...? In any case, the EXCLUDES replacement variable will work as it was last described. Let's see how the next live run goes. Tom
    125,585 pointsBadges:
    report
  • VGP
    I am working now!!!! Thanks Sorry guys, I had not posted the full QM to keep it simple and I have a CASE statement that was AFTER the Excludes in my QM but BEFORE it in the Command and RPG call. When I corrected the order of those...wala the Excludes works!! The extra comma was my cut and paste and typing. Thanks so much for the help with the proper Quotes and syntax. VGP
    300 pointsBadges:
    report
  • TomLiotta
    As might be evidenced by this question, "simplification" sometimes complicates things. But I understand. Glad it's working. Tom
    125,585 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