RE: SQL create view statement too long or complex

Thanks for the clarifications, Chuck.
I rather like the "FOR SYSTEM NAME" clause as the alternative to the V_INT00001 name that we get without it.
Except that I can't get the Great View Creator to play along. Keyword FOR not expected... Was that capability provided through a recent PTF that our SysAdmin might have missed? (InfoCenter document has that reference surrounded by chevrons).
If that's the case, does anyone know of what PTF group I should suggest he acquire?

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-
bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, October 23, 2013 2:15 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: SQL create view statement too long or complex

On 10/23/13 8:51 AM, Luis Rodriguez wrote:

At a quick glance I think I can find some issues with your SQL
statement:
* You should not specify column names after the "VIEW" (the ones

that

appear between parentheses).

That syntax is perfectly acceptable. However coding additionally
the "AS column-name" also in the select-clause is redundant or
pointless.
In the given statement, the parenthetical column-list overrides the
naming within the select-clause of the SELECT statement that defines
the VIEW query.

In fact, using that column-list syntax is often preferable, because
then both the SQL and the system [or long and short, or ALIAS and
standard] column-names can be specified [similar to the newer syntax to
effect the same for the file names]:

CREATE VIEW DEVLIB/V_InternetProfiles FOR SYSTEM NAME V_INETPRFS
( PROFILEID
, "iNID_Model" FOR iNID_Model
, "Is_Dynamic" FOR IS_Dynamic
, "Speed_Down" FOR Speed_Down
, "Speed_Up" FOR Speed_Up)
AS ( select ...

* Remove the quotes of the column names. IE, use AS iNID_Model
instead of AS 'iNID_Model'.

To be clear, "iNID_Model" is very conspicuously different than
'iNID_Model', and is similarly different in meaning to the DB2 for i
SQL which uses the double-quote as the delimiter for identifiers, not
the apostrophes as delimiters, which are used for constants.

Again, this is perfectly acceptable usage. And as shown above,
allows assigning /both/ column name variants. As well, like Michael
noted in a followup reply, the identifier ATTRIBUTES is a "reserved
word" in the DB2 for i SQL, and thus to use that as a column name, the
token often must be delimited to avoid confusion with the reserved
word:
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzwordsre
.htm

To maintain case [i.e. avoid folding to upper], by default, the
column name must be delimited with double quotes. Some interfaces may
accept non-delimited mixed-case column names that are then passed to
the database since-delimited.

Regards, Chuck

<<SNIP>>

On Wed, Oct 23, 2013 at 10:44 AM, Koester, Michael wrote:

<<SNIP>>

CREATE VIEW DEVLIB/V_InternetProfiles (PROFILEID, iNID_Model,
IS_Dynamic, Speed_Down, Speed_Up) AS SELECT PROFILEID,
substr(profileid, 1, locate(' ', Profileid)) as "iNID_Model",
case when locate('RG_INET_VLAN = 800, ', "ATTRIBUTES") > 0
then 'FALSE'
when locate('RG_INET_VLAN = 600, ', "ATTRIBUTES") > 0
then 'TRUE'
else 'N/A'
end as "Is_Dynamic",
<<SNIP>>