Multiple SQL statements in a single source member

[ukpi1b]

Couple notes. To run multiple SQL statements from one source member you have to use RUNSQLSTM command with some limitations (simple SELECT and variables not allowed). Statements must be separated by ; like in your example. To run QMQRY from CL you must first create query via CRTQMQRY command. You will have to create 2 queries from two different source members. Check MCPress archive for QMQRY for some examples and variables usage.

[Guest.Visitor]

I cannot use RUNSQLSTM due to the fact that the SQL statement uses variables which I would pass from the CL.

[ukpi1b]

Create 3 QM queries - one for each file as you coded without ;. Run all from CL: STRQMQRY *LIBL/QRY1 SETVAR((LIB &LIB) (DATE &DATE)) STRQMQRY *LIBL/QRY2 SETVAR((LIB &LIB) (DATE $DATE)) STRQMQRY *LIBL(QRY3 SETVAR((LIB &LIB) (LAST &NBRRCD)) Make sure that all variables are character(use CHAR or DGITS to convert numeric) Check quotation marks usage and be carefull with RRN if file has deleted records and was not reorganized. Some examples were posted on ukpi1b "Using variable parms in STRQMQRY" 8/19/02 7:47am

[J.Pluta]

Can you generate the source member on the fly? Write a small RPG program to output the source statements using the variables passed in from the CL. Then execute the RUNSQLSTM command. Joe

[Guest.Visitor]

I am trying to put multiple SQL statements (with parms) in a single source member to be run from a CL. I am putting a semi-colon at the end of each statement, then compiling it with crtqmqry. But when I run it out of the cl using strqmqry, I get: Token ; was not valid. Valid tokens: . If I run the statements individually they work, but not when I put them together. Is there something that I am missing? Karen

---

Code

[Guest.Visitor]

Thanks for your input. There are a variety of methods that I could use: 1) Create 3 separate source members and have three STRQMQRY commands in the CL passing the necessary parms. 2) Create a dummy SQL source member that contains nothing but a parm and build the SQL statement in the CL and then run STRQMQRY for the dummy SQL, passing it the whole statement as a parm for each SQL statement that I need to run. 3)Put the three SQL statements into a small RPG program and call the RPG from the CL, passing it the necessary parms. I kind of like #2, because you can then re-use the dummy SQL. But what I really wanted was to be able to put all three into one source file that would accept parms. I guess what you guys are telling me is that it can't be done without some finageling. Thanks again for your input.