On 16 Nov 2012 08:32, Vernon Hamberg wrote:
<<SNIP>>
As to CREATE VIEW & RUNQRY - again, that is 2 steps for the user -
whereas using what all of us who have written - an EXCSQL or some
such - it is one step to get the results of a SELECT displayed.
That's all I want from both RUNSQLSTM and the new RUNSQL.
I agree... that the SELECT being able to produce a report OUTPUT(*)
would be nice; probably as a Default Form QM Query. But it is not like
the command is intended to be an end-user tool.? Surely a programmer,
for whom the tool is likely intended, they can deal with the issue as
they see fit.?
For a SELECT statement, my EXECSQL tool did the CREATE VIEW, reverted
to CREATE TABLE AS if an ALIAS prevented the CREATE VIEW request,
performed the RUNQRY QRY(*NONE), then DROP VIEW or DROP TABLE
accordingly. And even though my command gave that capability, I would
not be too concerned for the lack of the reporting function via the IBM
command, perhaps because I have long been accustomed to the two-step
processes of both OPNQRYF\CPYFRMQRYF, OPEN\FETCH, and...
For RUNSQLSTM scripts I do effectively the same thing when I want a
report:
set current schema qtemp
;
; -- <-- -- -- -- -- Scenario A -- -- -- -- -->
; -- A1> instead of select which is unsupported statement:
create table r2(a , d1 , d2 ) as
( with dgrp (a, d1, d2) as
(select a, min(d), max(d) from d group by a)
select m.a, d.d1, nullif(d.d2,d.d1)
from m left join dgrp as d
on m.a=d.a
) with data
; -- A2> report on the result:
call execcmd('runqry *n qtemp/r2')
; -- optionally delete the temporary object:
drop table r2
;
; -- <-- -- -- -- -- Scenario B -- -- -- -- -->
; -- B1> instead of select which is unsupported statement:
create view qtemp/myScriptView ( rrn1 , line ) as
( select rrn(a),line from daft a where line like ' 2%')
; -- B2> report on the result:
call execcmd('strqmqry SltStar setvar((VIEW ''qtemp/myScriptView''))')
; -- the above QMqry is: select a.* from &VIEW as a
; -- optionally delete the temporary object:
drop view myScriptView
;
midrange.com
