× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. October 2013

RE: SQL create view statement too long or complex

Victory has been declared!
I wrapped each of the column derivations with char(expression, appropriate-length) and got exactly what I needed.

CREATE VIEW DevLib/V_InternetProfiles
AS SELECT PROFILEID,
char(substr(profileid, 1, locate(' ', Profileid)), 16)
as "iNID_Model",

char((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), 5) as Is_Dynamic,

char((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), 8) as Speed_Down,

char((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), 8) as Speed_Up

FROM ProdLib/epprofiles
WHERE "ATTRIBUTES" like '%profileType = RG Internet%'
-- Adjusted w/Luis' suggestions
View V_INTERNETPROFILES created in DEVLIB.

I owe you a beer, Luis!
On with my day...
-- Michael

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

Michael,


What would be the maximun length possible for your "Speed" columns? Do
you need the full "ATTRIBUTES" lenght (ie, 16384)?

What happens if you CAST them to a smaller size? For example:

CAST(
(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 CHAR(64)) as Speed_Down,

I wrote a quick test and it created the view...
Regards,
Luis

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



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

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.


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



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 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.