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>>