Using RUNSQLSTM in a CL program

pts.
Tags:
SQL
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 6  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
  • Chance
    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...
    105 pointsBadges:
    report
  • WaltZ400
    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.
    655 pointsBadges:
    report
  • RussK1
    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.
    0 pointsBadges:
    report
  • Mayleong
    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
    0 pointsBadges:
    report
  • TheQuigs
    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.
    0 pointsBadges:
    report
  • JPLamontre
    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
    0 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