|
-----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-)too long/complex (Reason 3).
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
of
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
key/value pairs. (Don't confuse my use of the term, key, with thethis:
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
... RG_INET_VLAN = 600, childrenKeys = , INET_DOWN_RATE = 20480,mess
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
of substring and locate functions to isolate the goods.laptop memory.
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
4. The problem is not limited to the interactive SQL environment; Ithreshold,
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
I'll likely encounter the issue again shortly).L@xxxxxxxxxxxx
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-
To subscribe, unsubscribe, or change list options,l.
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-
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.