MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » November 2012

Re: RUNSQL Command: Help & Escape Messages



fixed

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
;






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact