Query Manager Error

150 pts.
Tags:
AS/400
SQL Query
SQL Server
V5R4M0
Type SQL Statement ************************** Beginning of Data ************************* 0001.00 SET MISC :MISC6 0002.00 SELECT bcbgcd COLHDG("MISC" "CODE"), 0008.00 &MISC LEN(4,0) COLHDG("SHARED CONTAINER" "CODE") NAME(MISC) 0009.00 FROM lmbcrela 0012.00 WHERE bcbgcd=&MISC In checking the SQL syntax it errors: Token MISC was not valid. Valid tokens: :.

Software/Hardware used:
V5R4M0 AS/400
ASKED: September 10, 2010  4:42 PM
UPDATED: September 16, 2010  2:20 PM

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: 8  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
    I don't quite understand what you are trying to do. If this is a QM query, then what is this statement doing in there:
    0001.00 SET MISC :MISC6
    SET <variable> isn't a valid QM query statement. And if it was, it wouldn't have a syntax that looked that way. What are you trying to do? Tom
    125,585 pointsBadges:
    report
  • a1maida
    I am trying to pass a numeric parameter.
    150 pointsBadges:
    report
  • TomLiotta
    I am trying to pass a numeric parameter. Fine. Then pass a numeric parameter. It's not a problem. But what is the SET statement for? You already have a replacement variable in the WHERE clause that would receive your numeric parameter. You don't need a SET statement for anything. The value will be set when you run the STRQMQRY command. Also, you have &MISC at the beginning of statement 0008.00. It doesn't seem appropriate there, especially if it's going to be a numeric value. You need to replace &MISC in statement 0008.00 with a column name from your table. (Or are you trying to reference the ordinal column number?) Maybe the best thing would be if you showed us what you want your final SQL statement to look like when it executes. Tom
    125,585 pointsBadges:
    report
  • NarasimhaReddy
    First item, Can we execute 2 sql statements thru one QMQRY ? What do you want to achieve thru Set statement ? The one you provided specifies nothing. The Set should be used to set connection or set transaction.
    225 pointsBadges:
    report
  • a1maida
    I am trying to compile this CLP: 100- PGM PARM(&MISC#) 102- DCL VAR(&MISC#) TYPE(*DEC) LEN(4 0) 103- DCL VAR(&MISC) TYPE(*DEC) LEN(4 0) 122- 128- STRQMQRY QMQRY(AXMTESTLIB/SANQMCON1) OUTPUT(*) + 130 ALWQRYDFN(*YES) SETVAR((&MISC &MISC#)) * CPD0776 30 Variable &MISC for parameter SETVAR must be *CHAR. * CPD0776 30 Variable &MISC# for parameter SETVAR must be *CHAR. 133- 137- MONMSG MSGID(QWM2701) EXEC(RETURN) 138- 2900- END: RETURN
    150 pointsBadges:
    report
  • a1maida
    With SETVAR you cann't pass numeric parameters.
    150 pointsBadges:
    report
  • TomLiotta
    Ah! That CL example explains better what you're trying to do. Here, try it this way:
    pgm  ( &MISC# )
    
       dcl   &MISC#       *dec  (  4 0 )
       dcl   &MISC        *char    4
    
       chgvar      &MISC        &MISC#
    
       strqmqry    qmqry( AXMTESTLIB/SANQMCON1 ) output( * ) +
                     setvar( ( MISC &MISC ) )
    
       monmsg    ( QWM2701 ) exec( RETURN )
    
    END:
      return
    
    endpgm
    I reformatted for readability and made a couple simple changes. Your &MISC# variable is being passed as a numeric parm, and that's fine as long as it was generated in the calling program as *DEC (4 0) or a compatible data type in a different language. But when it is used by the STRQMQRY command, it needs to be a series of character digits. We do that by creating a *CHAR variable that is 4 bytes long and letting the CHGVAR command do a straight conversion. Bear in mind that the CL variable named &MISC has no relationship at all with your QM replacement variable of the same name. It's probably not a good idea to use the same name for both, but it will work. It just brings potential confusion when errors come up. I also got rid of the ALWQRYDFN() parameter. Since you're creating a QM query, you don't want some *QRYDFN object of the same name getting into this. I'd make other changes, but they aren't actually required for this question. And your QM query statement becomes something like:
    SELECT bcbgcd COLHDG("MISC" "CODE"),
           somecolumn LEN(4,0) COLHDG("SHARED CONTAINER" "CODE") NAME(MISC)
      FROM lmbcrela
        WHERE bcbgcd=&MISC
    I replaced the "&MISC" for the second column with a column name of "somecolumn", though I think I can see why you had it there. If you really wanted a column from a table, then use the name of that column. You might even put &MISC back and have it work. It doesn't make much sense, though, for this SELECT statement since the same value is going to show up in the bcbgcd column anyway. Your WHERE clause guarantees that both columns will be nothing but repeats of the value in &MISC -- if 1000 rows match, then that value will display 2000 times and nothing else will show up. I assume that you will develop the SELECT statement over time to make it more meaningful. This should get you closer. Tom
    125,585 pointsBadges:
    report
  • a1maida
    It worked! Thank you Tom for your solution!
    150 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