Re: 57nnQU1

Jim

True enough - and that's the usual way we're told that we can use SELECT.

But I'm not talking about using SELECT __within__ another statement - I'm talking about SELECT on its own, as in

RUNSQL SQL('selct * from qiws/qcustcdt')

with an output option of * - display. All the examples we've written that use QM have this capability. And it is, for me, useful in a program to show ad hoc content - I'm sure many of us have you DSPPFM or RUNQRY in a program - RUNSQL could have the same usage. But not as written by IBM.

As an interactive command line usage, I find it useful to use SQL to check what's in a file - and being able to filter what I see, whether the column set or the selectivity - SELECT is perfect for this - and I don't want to have to enter STRSQL to do this - minimizing keystrokes. And that's why the answer from IBM is not enough for me - sure, I can do and INSERT or CREATE TABLE with RUNSQL - but then I have to do DSPPFM of RUNQRY () to see the results.

The output options on the home-brew versions typically also have output options of *PRINT and *OUTFILE - not so with IBM's RUNSQL - and these have been very useful for me, as well.

Thoughts?

Vern

On 9/25/2014 7:50 PM, Jim Franz wrote:

Vern,
Am curious about your statement "The IBM one does not do SELECT statements,
as neither does RUNSQL..."

Since using the RUNSQL statement in a CLLE could be batch or interactive,
one has to provide where the Select stmt results will be placed.
This works fine for me (the statement was built from cl variables:

RUNSQL SQL(&STMT) COMMIT(*NONE) NAMING(*SYS)

(joblog of actual execution)

RUNSQL SQL('INSERT INTO TEST/MDC001PE
(SELECT * FROM MDC001P
WHERE ncsedt=20140925 and ncsetm=163932 and ncsvc=''MDC'')')
COMMIT(*NONE) NAMING(*SYS)

The developerworks wiki has samples of RUNSQL with "Select"
https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/Run%20SQL%20(RUNSQL)%20-%20new%20command

Jim Franz


On Thu, Sep 25, 2014 at 6:33 PM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
wrote:

Good point, Sam - many of us have written such a command over the years -
I think I got my first sample from Buck in these lists. These all take
advantage of the SETVAR parameter of STRQMQRY and have a QMQRY that is made
up only of substitution variables.

The IBM one does not do SELECT statements, as neither does RUNSQL. I
haven't got a good answer as to why not, so I've given up.

So whoever uses the code in the wiki, probably change the command name to
something like EXCSQLSTM.

Cheers
Vern


On 9/25/2014 3:35 PM, Sam_L wrote:

FWIW: There is an IBM RUNSQLSTM command that is not compatible with the
one in the Wiki.

Sam

On 9/25/2014 1:34 PM, Voris, John wrote:

The RUNSQLSTM is a great command and helps move you into the SQL world
with training wheels.
http://wiki.midrange.com/index.php/SQL (which uses SQL syntax and the
QM engine.)

From an SQL statement like RUNSQLSTM ' SELECT A.*, B.* from FileA A

join FileB B on KeyA = KeyB '

and using F4 to name your output file, there is no need to crank up old
WRKQRY to get an *OUTFILE.
You can do this with the custom RUNSQLSTM command.

---
This email is free from viruses and malware because avast! Antivirus
protection is active.
http://www.avast.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.