RE: SQL create view statement too long or complex

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-
bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Wednesday, October 23, 2013 5:09 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: SQL create view statement too long or complex

It'll either be the database group ptf or one of the technology
refreshes - I suggest going to www.ibm.com/developerworks/ibmi and look
for the link to technology updates or the like, then burrow a little
more and look for DB2.

On 10/23/2013 3:48 PM, Koester, Michael wrote:

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

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

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-
L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.