Using RUNSQLSTM in a CL program
When I'm running the following SQL statement in my CL program, I get an error "Function check. SQL9010 unmonitored by @SQLCLP..." (@sqlclp is the name of my CL object I'm executing)
Here is the SQL statment I'm trying to run.
---------------------------------------------
SELECT a.rmbx# mailbox, DIGITS(a.r1dtrc) received,
DIGITS(a.r1tmrc) time,
SUBSTR(c.r4qui2,1,12) PO#,
SUBSTR(d.r6rfid,1,10) Release#,
SUBSTR(e.rhqunm,1,10) ShipTo, d.r6fq FirstC,
SUBSTR(c.r4qui1,1,11) item#, d.r6qty quantity, c.r4um unit,
SUBSTR(d.r6dat1,5,2) || '/' || SUBSTR(d.R6dat1,7,2) deliver
FROM efqpsd43/rlh1p a, efqpsd43/rlhn1p b, efqpsd43/rldlinp c,
efqpsd43/rldfstp d, efqpsd43/rldn1p e
WHERE a.rtpid like '054481205%' AND a.r1dtrc between 20050908 and
20050909
AND b.rtpid = a.rtpid AND b.rmbx# = a.rmbx#
AND c.rtpid = a.rtpid AND c.rmbx# = a.rmbx#
AND d.rtpid = a.rtpid AND d.rmbx# = a.rmbx#
AND e.rtpid = a.rtpid AND e.rmbx# = a.rmbx#
AND d.r6li# = c.r4li# AND d.r6lp#2 = c.r4lp#2
AND d.rtpid = a.rtpid AND d.rmbx# = a.rmbx#
AND d.r6li# = c.r4li# AND d.r6lp#2 = c.r4lp#2
AND d.r6lp#2 = c.r4lp#2 AND d.r6lp#3 = c.r4lp#3
AND e.rhli# = d.r6li# AND e.rhlp#2 = d.r6lp#2
AND e.rhlp#3 = d.r6lp#3
AND e.rtpid = a.rtpid AND e.rmbx# = a.rmbx#
AND e.rhli# = d.r6li# AND e.rhlp#2 = d.r6lp#2
AND e.rhlp#2 = d.r6lp#2 AND e.rhlp#3 = d.r6lp#3
AND b.rborid = 'MI'
AND e.rhorid = 'ST'
-------------------------------------------------
Is there a problem with using the Select statement? I saw something mentioned about a caveat to using the Select statement but I had to be a memeber of a certain site in order to read more about it.
Any help is appreciated.
Thanks.
Mark
Software/Hardware used:
Software/Hardware used:
ASKED:
September 9, 2005 10:01 AM
UPDATED:
September 13, 2005 3:21 PM
Answer Wiki:
I noticed in your where clause, the first statement happens to be a condition. Using standard SQL pratice, In your WHERE clause, It's recommended that you create your file joins before any conditions. I hope this helps..
To see all answers submitted to the Answer Wiki: View Answer History.
You can’t use a SELECT statement with RUNSQLSTM.
You can do an INSERT, however, so maybe you could create a temp file that you could INSERT the result set into…
Another possibility is to insert your SELECT into a QMQRY and then do a STRQMRY to an outfile. The output file does not need to be defined ahead of time as it will create it on the fly.
As has been mentioned, you can’t use a SELECT statement in RUNSQLSTM unless it’s in an INSERT. I believe you still need the SQL Development kit to use Query Manager, but if you have it, it works well.
There was a utility, called EXCSLQSTM, published by Midrange Computing: http://www.mcpressonline.com/mc?50@34.DLqUcK7ty17.2@.5bfa46dd
I’ve used it for years. HTH.
Just like to add that in AS400 u can’t use “BETWEEN” as it wont interpret the statment
If u execute the same statement under SQL session usign “BETWEEN”(STARTSQL to execute the same statement) then it works!
WHERE a.rtpid like ’054481205%’ AND a.r1dtrc between 20050908 and
20050909
Use instead:
WHERE a.rtpid like ’054481205%’ AND a.r1dtrc >= 20050908 and a.rldtrc
I would check out the EXCSQLSTM command mentioned above.
Also, I don’t know where the other person got the idea that the BETWEEN wouldn’t work. I use it all the time.
when using a select in a runsqlstm, you have to give to sql the name of the output file in the sql itself : runsqlstm don’t provide the option :
if outfile already exist
insert into … select …
if outfile don’t exists
create table … as select …
need a sample ? some other solutions ? goto http://jplamontre.free.fr/AS400/EXECUTESQL.htm