Re: RUNSQL Command: Help & Escape Messages

[Sam_L <lennon_s_j@xxxxxxxxxxx>]

In defense of not supporting SELECT, there is quite a bit more coding 
effort to provide support for where the output of the select goes to.

Now this variation on Rob's select does work:

RUNSQL SQL('create table qtemp/zz_table as (SELECT SYSTEM_TABLE_SCHEMA, 
count(*) as the_count FROM syscolumns WHERE system_table_schema=''QIWS'' 
GROUP BY SYSTEM_TABLE_SCHEMA) with data') COMMIT(*NONE)

Basically it amounts to adding a create table and giving a name to the 
count(*) column.

And you can also do the same kind of thing with RUNSQLSTM.

Sam

On 11/14/2012 8:17 AM, rob@xxxxxxxxx wrote:
> Does RUNSQL support a simple select?  When I run this:
> runsql 'SELECT SYSTEM_TABLE_SCHEMA, count(*)
>                 FROM syscolumns WHERE system_table_schema=''ERPLXF''
>                          GROUP BY SYSTEM_TABLE_SCHEMA'
>
> I get nothing.  Nothing returns to my screen.  No joblog information.
> Nothing.
> Command Entry
> > CHGJOB LOG(4 00 *SECLVL) LOGCLPGM(*YES)
> > runsql 'SELECT SYSTEM_TABLE_SCHEMA, count(*)
>                   FROM syscolumns WHERE system_table_schema=''ERPLXF''
>                            GROUP BY SYSTEM_TABLE_SCHEMA'
>
> But this works fine
> > runsql 'CREATE TABLE ROB/NATHANs (WTF CHAR (5 ) NOT NULL WITH DEFAULT)'
>    Ownership of object NATHANS in ROB type *FILE changed.
>
> Guess not:
> http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fsqlp%2Frbafyrunsql.htm
>
> What a major disappointment.  In the stuff that uses wrap arounds for
> STRQMQRY you can use a simple select.  But when I tried to write a
> imbedded sql in RPG type solution I think I had the same limitation that a
> simple select (without an into or some such thing) wouldn't work.  IBM
> probably ran into the same thing.  I guess there's still situations for
> the renamed command I use to run an sql statement.
>
>
>
> Rob Berendt