On 20-May-2016 14:33 -0500, Buck Calabro wrote:
<<SNIP>>
Apparently the following is a script for Run SQL GUI; as Run SQL
Statements (RUNSQLSTM) source, several statements from the full script
[link at bottom of quoted message] are still unsupported AFaIK.
cl: chglibl (libsql1 libsql2) (*crtdft); /* <ed: set CURLIB> */
-- this goes into QGPL
create table table_master
(id int,
text char(25));
Minor revision <ed:> inline above, to fully handle assumptions.
Another revision is, reinserting just below, the missing statements [as
unquoted text], taken from the full script, so the effect [i.e. "goes
into LIBSQL1] of the next [quoted] statement makes sense, and so the
final statement finds every referenced column name instead of failing:
-- delete the QGPL copy and put it into one of the test libraries
drop table qgpl.table_master;
-- qualify these tables' creation
create table libsql1.table_master
(id int,
text char(25),
foreign_key int);
-- this goes into LIBSQL1
create view view_zero
(id, text)
as (select id, text from table_master);
-- make a child table in a different library in *LIBL
create table libsql2.table_child2
(id int,
text char(25));
-- this goes into LIBSQL2
create view view_two_subselect
(m_id, m_text, c_id, c_text)
as (select m.id, m.text, m.foreign_key
, (select text from table_child2)
from table_master m);
Three separate CREATE statements, three separate destinations.
Is there a reference for what is supposed to happen? <<SNIP>>
That is answered already in other fup replies. However the intention
of the topic seems instead...
Supposing the intent is to get the CREATE VIEW to create the view in
QGPL [as the effective current library] much like most CRTxxx commands
and many other CREATE statements would for an unqualified object-name,
consider the following revised script, composed using chicanery:
<code>
cl: chglibl (libsql1 libsql2) (*crtdft);
-- following creates into QGPL
create table table_master
( id int
, text char(25)
, foreign_key int
)
;
-- following creates child table in 2nd lib in *LIBL
create table libsql2.table_child2
( id int
, text char(25)
)
;
-- following creates into QGPL; no table-references
create view "curlib()"
(intcol) as ( values(1) )
; -- or use: create table "curlib()" (intcol int);
-- following creates into QGPL instead of LIBSQL2
create view view_two_subselect
(m_id, m_text, c_id, c_text)
as ( with
crt_into_curlib as
( select intcol from "curlib()" )
select m.id, m.text, m.foreign_key
, (select text from table_child2)
from table_master m
)
;
</code>
Note: The subterfuge in the above is first ensuring that the first
table-reference in the VIEW is from the current library and second that
the fact that the table-reference is unused ensures the reference is
optimized-out of the plan, despite being required to remain in the
based-on\dependencies for the VIEW. As coded, there appears to be one
[extra] row of data; the "curlib()" could be created as an empty table
instead, to avoid that anomaly, but not to avoid the other nuances
involving the /bogus/ table-reference.
Again supposing the intent is to get the CREATE VIEW to create the
view in QGPL [as the effective current library] much like most other
CRTxxx commands or many other CREATE statements would for an unqualified
object-name, consider the following revised script which uses a
compound-statement with a variable to assign the library name in a fully
dynamic statement:
<code>
begin
declare bsv_libl varchar(276) default '*SAME' ;
declare cur_lib varchar( 10) default '*SAME' ;
set bsv_libl = 'libsql1 libsql2' ;
set cur_lib = 'qgpl' ;
call qcmdexc ( 'chglibl (' concat bsv_libl concat ')'
concat ' (' concat cur_lib concat ')' )
;
-- following creates into QGPL
create table table_master
( id int
, text char(25)
, foreign_key int
)
;
-- following creates a table in 2nd lib in *LIBL
create table libsql2.table_child2
( id int
, text char(25)
)
;
-- following creates into QGPL rather than LIBSQL2
execute immediate
'create view ' concat cur_lib concat
'.' concat 'view_two_subselect
(m_id, m_text, c_id, c_text)
as (select m.id, m.text, m.foreign_key
, (select text from table_child2)
from table_master m
)'
;
end
</code>
Note: As a compound statement, the ease of the full script error
handling being controlled by the ever-simple Severity Level (ERRLVL)
parameter of RUNSQLSTM is lost; the above has no SQLEXCEPTION or any
other [continue] handlers to deal with errors, such as -601 "already
exists." The scripting could be redefined in some creative ways to make
only the setup\execution of the CREATE VIEW statement(s) run under
compound statements.
Note: As I noted in another message, because I can not test, the
above execute immediate may require [still on v7r2] the same as on v7r1,
whereby the /expression/ must be a variable; coded above, as though the
support is there, as I infer from the docs, to specify just a
character-string instead of a variable name.
Full example script at [http://code.midrange.com/c28c3aef48.html]
midrange.com
