Error when executing RUNSQLSTM from CL program

5 pts.
Tags:
AS/400
AS/400 errors
CL
RUNSQLSTM
SQL
I am trying to use the RUNSQLSTM command. I get the error message "statement not allowed" Not really trying anything fancy. In CL program RUNSQLSTM SRCFILE(jwe/QCLSRC) SRCMBR(SQL1) + COMMIT(*NONE) In SQL1 I have SELECT mstid, SUBSTR(ftxref,1,1) AS ftxref_1, SUBSTR(HEX(SUBSTR(ftxref,2,7)),1,9) AS char, ftext, ftxtno FROM MEDPDATA/ftxp010 WHERE SUBSTR(ftxref,1,1) NOT IN ('A','M') AND ftxref NOT IN (SELECT scrcod FROM tabp160) UNION SELECT mstid, SUBSTR(ftxref,1,1) AS ftxref_1, SUBSTR(ftxref,2,7) AS char, ftext, ftxtno FROM MEDPDATA/ftxp010 WHERE SUBSTR(ftxref,1,1) IN ('A','M') AND ftxref NOT IN (SELECT scrcod FROM tabp160)
ASKED: May 6, 2008  3:32 PM
UPDATED: April 20, 2010  7:32 AM

Answer Wiki

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

You cannot run a SELECT using RUNSQLSTM.

==============================================

SELECT is not allowed in RUNSQLSTM simply because it’s meaningless. Once selected, there is no facility in RUNSQLSTM actually to do anything with the selected rows and columns. If a SELECT is intended, then it must be embedded in some program that can perform the necessary I/O to do what’s needed with that selected set.

STRQMQRY provides a means of displaying or printing the selected set or copying them to another file.

RUNSQLSTM is for SQL statements that can perform actions in themselves — e.g., INSERT, UPDATE, CREATE PROCEDURE, etc.

Tom

Discuss This Question: 1  Reply

 
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
  • Gilly400
    Oops, I hadn't noticed that it was a SELECT from RUNSQLSTM, I thought it was an embedded SELECT. So I guess this'll work OK from STRSQL. Dave is right, RUNSQLSTM doesn't know what to do with output from a SELECT.
    23,730 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