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