× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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]



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.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 on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.