150 pts.
 Query Manager Error
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
  Help
 Approved Answer - Chosen by MelanieYarbrough

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

ANSWERED:  Sep 15, 2010  8:14 PM (GMT)  by MelanieYarbrough

 
Other Answers:
Last Wiki Answer Submitted:  June 29, 2012  7:50 am  by    0 pts.
Latest Answer Wiki Contributors: 
To see other answers submitted to the Answer Wiki: View Answer History.


Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _


 

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

 108,300 pts.

 

I am trying to pass a numeric parameter.

 150 pts.

 

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

 108,300 pts.

 

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 pts.

 

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 pts.

 

With SETVAR you cann’t pass numeric parameters.

 150 pts.

 

It worked!
Thank you Tom for your solution!

 150 pts.