RE: SQL create view statement too long or complex -- RPG400-L

Thanks Luis. I incorporated your suggestions:
- Removed the column names from the Create View. That makes sense as they are established in the Select.
- Removed the quotes from the AS column names. Left quotes around all occurrences of "Attributes". Turns out that is a reserved word in sql.
- Enclosed Case structures with parens. I like that idea. Thanks.
- The trailing ; is not used in the interactive sql, but I had missed that in the QSQLSRC source. Good catch!

That said, I still get the "SQL statement too long or complex." error. Can't declare victory just yet.

-- Michael

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

Mike,

At a quick glance I think I can find some issues with your SQL
statement:
* You sdould not specify column names after the "VIEW" (the ones that
appear between parentheses).
* Remove the quotes of the column names. IE, use AS iNID_Model
instead of AS 'iNID_Model' .

* As a personal style issue, I like to enclose my CASEs statements
with parentheses. For example:
(CASE .....
END ) AS xxxxx

Don't forget to put a semi-colon (;) at the end of your SQL sentence
(after
Internet%')

HTH,

Luis

Luis Rodriguez
IBM Certified Systems Expert - eServer i5 iSeries
--

On Wed, Oct 23, 2013 at 10:44 AM, Koester, Michael
<mkoester@xxxxxxxxxxxxx>wrote:

I'm trying to create an SQL View to select a small set of (mostly-)
derived columns from a small number of rows from a table that is
rather unstructured. The view would return columns from certain
values parsed out of a long string.

When I compose the CREATE VIEW command in interactive sql, it throws
the
SQL0101 (SQL statement too long or complex.) error.
So I pare down the statement to see if just the Select part returns
results, and get the same error on that.
So I pare down the Select to return fewer columns until I get
something, and verify that each of the result columns are
independently correct. But when I put the pieces back together it's

too long/complex (Reason 3).

I'm hoping there is a "less complex" way to get what I need, because
having this parsing take place in a view would keep a lot of
complexity out of the RPG code.

The table, "epProfiles", is imported to the i daily, early in the day
when other activity is light, and that data remains static until it
gets reloaded the next day. Each row consists of a unique ProfileID,
and a second varchar column, "Attributes", which contains a variety

of

key/value pairs. (Don't confuse my use of the term, key, with the
record key). The key strings are terminated by " = "; the value
follows the " = " and ends with a comma (or end-of-row).
A sample segment of data from the "Attributes" column might look like

this:

... RG_INET_VLAN = 600, childrenKeys = , INET_DOWN_RATE = 20480,
RG_INET_VPI = 0, isGroup = false, RG_LOCAL_VLAN = 2, atiText = ,
failureThreshold = 1, pollInterval = 1800, INET_DOWN_BURST = 67108,
... and so on for another 10,000 characters on some rows.

As you can see from my sql statement shown below, it's a bit of a

mess

of substring and locate functions to isolate the goods.

Pertinent details:
1. i/OS v7.1
2. EPPROFILES file currently has 197 records and is DDS defined, as
follows:
A R PROFILESF
A PROFILEID 64A COLHDG('ProfileName')
A ATTRIBUTES 16384A COLHDG('Attributes')
A VARLEN(5000)
A K PROFILEID

3. I have a very limited familiarity with Navigator, and have never
used it for any SQL development, mostly because of insufficient

laptop memory.

4. The problem is not limited to the interactive SQL environment; I
put the CREATE VIEW statement into a QSQLSRC source member and
experienced the same failure with RUNSQLSTM against that source.
5. If I can overcome this obstacle, there will likely be several
similar views that I'd like to create over the epProfiles table
(meaning if the solution gets me just under the too-complex

threshold,

I'll likely encounter the issue again shortly).

Oh, one other clue: by removing either the Speed_Down or Speed_Up
case structures, the statement becomes acceptably less complex, and
creates the view (but without one of the desired columns).

I confess to being an SQL novice, but I welcome any suggestions.

~~~~~~~~~~~~~~~~~
SQL statement:
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",

case when locate('INET_DOWN_RATE = ', "ATTRIBUTES") = 0
then 'N/A'
else substr("ATTRIBUTES",
locate('INET_DOWN_RATE = ', "ATTRIBUTES") + 17,
locate(', ', "ATTRIBUTES",
locate('INET_DOWN_RATE = ', "ATTRIBUTES") + 1)
- (locate('INET_DOWN_RATE = ', "ATTRIBUTES") + 17))
end as "Speed_Down",

case when locate('INET_UP_RATE = ', "ATTRIBUTES") = 0
then 'N/A'
else substr("ATTRIBUTES",
locate('INET_UP_RATE = ', "ATTRIBUTES") + 15,
locate(', ', "ATTRIBUTES",
locate('INET_UP_RATE = ', "ATTRIBUTES") + 1)
- (locate('INET_UP_RATE = ', "ATTRIBUTES") + 15))
end as "Speed_Up"

FROM datalib/epprofiles
WHERE "ATTRIBUTES" like '%profileType = RG Internet%'
~~~~~~~~~~~~~~~~~

Many thanks!

Michael Koester
Programmer/Analyst

DataEast

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

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