Error when executing RUNSQLSTM from CL program

Tcwalls

5 pts.

Tags:

AS/400

AS/400 errors

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 Last updated: April 20, 2010 7:32 AM

Answer Wiki

Last updated: April 20, 2010 7:32 AM GMT

Dave.andruchuk45 pts.
History
Contributors
- Ordered by most recent
- Dave.andruchuk45 pts.
- Gilly40023,730 pts.

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

Gilly400 May 7, 2008 2:07 PM GMT

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