Re: SQL *SYSTEM naming default schema rule?

On 5/20/2016 4:05 PM, Charles Wilt wrote:

Buck take a look at the CREATE VIEW statement itself...

If SQL names were specified, the view will be created in the schema
specified by the implicit or explicit qualifier.

If system names were specified, the view will be created in the schema that
is specified by the qualifier. If not qualified and there is no default
schema, the view name will be created in the same schema as the first table
specified on the first FROM clause (including FROM clauses in any common
table expressions or nested table expression). If no tables are referenced
in the fullselect, the view will be created in the same schema as the first
user defined table function. If no table or user defined table function is
referenced in the fullselect, the current library (*CURLIB) will be used.

I think you're running into differences because you've got SQL & System
names intermixed.

table_master --> SQL name
view_zero --> valid system name
table_child2 --> SQL name
view_two_subselect --> SQL name

Thanks! The Fine Manual at CREATE VIEW describes the situation - if I
ignore the bit about SQL names. A quick test shows that the CREATE with
the subselect always gets created in the library of the subselect, no
matter if the view's name is long or short.

That's because the first table physically present in the CREATE VIEW
statement is in the subselect; physically before the FROM clause. If
I'd used a JOIN, the results would be that the table in the FROM clause
(LIBSQL1)

-- this one goes into LIBSQL1 because table_master is
-- physically the first table referenced, and
-- it is in LIBSQL1
create view view_2s_join
(m_id, m_text, c_id, c_text)
as (select m.id, m.text, m.foreign_key, c.text
from table_master m
left join table_child2 c on m.foreign_key = c.id);


Best guess? Where it says 'SQL names were specified...' really means
'*SQL NAMING was specified...' I can't see how to test this because the
production view needs to use tables in two different libraries (thus
*SYSTEM naming and *LIBL). SET SCHEMA won't help because I will then
have to qualify one of the tables referenced in the view, and the whole
point of this exercise was to avoid that. The plan was to make it
portable across test and production.