RE: SQL create view statement too long or complex

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

Michael,

Have you been able to create a view with just the Speed_Down and
Speed_Up Case Structures ?

Maybe a join between two views is workable ?

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-
bounces@xxxxxxxxxxxx] On Behalf Of Koester, Michael
Sent: Wednesday, October 23, 2013 9:14 AM
To: RPG400-L@xxxxxxxxxxxx
Subject: SQL create view statement too long or complex

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.