× 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. May 2016

Re: SQL Statement length

Tyler I respect your opinion and any other opinion, but me myself
personally, would not create a big sql in this way and be aware there are
many way to "skin a cat", believe me sooner or later this is going to
be a problem for any other programmer even the same who created if any
change need to be done in this sql, is not something in regard to improve
performance is something in regard to programming ethic and thinking on any
other programmer that will come back to fix this sql in a future, I'm
not criticizing anyone but if your sql statement is so big you should think
about it if anything need to be done in a future when maybe you are no
there or could be when you don't even remember what you did or why you did
it. Point of my opinion.


On Thu, May 12, 2016 at 4:52 PM, Tyler, Matt <matt.tyler@xxxxxxxxxxxxxx>
wrote:

" sql in modules or pieces better said and for every piece I would produce
"
This seems like over kill unless you are planning on using that section of
SQL in multiple places then its best to convert into a view, user function
or procedure. The query engine is really good at optimizing very large
dynamic query statements and doing it very quickly. I have a current one
that can be up to 8000 characters long. I need return times to be below 5
seconds which our system does. I don't know how long it would run if I
split it up into multiple temporary tables inside additional program stack
entries but I doubt it would improve the process.

I say instead of criticizing the SQL statement style used because it's not
your style is wrong let's get to the bottom of the OP question first.


Thanks, Matt


-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jose
Perez
Sent: Thursday, May 12, 2016 2:45 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: SQL Statement length

I Agree with Paul, 6000 Characters for a query? that will make your
program very complicated, what I would do if I were you I would work my sql
in modules or pieces better said and for every piece I would produce the
result in global temporary table and work from my global temporary table,
it is not a good practice to have a so big sql in just one line.

On Thu, May 12, 2016 at 4:37 PM, <paultherrien@xxxxxxxxxxxxxxxxxx> wrote:

The length of your statement may be a red-herring. Are you getting
the error on compile or on execution?
Is the returned data being truncated in one of your receiving variables?


Paul

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
Graves, Chuck
Sent: Thursday, May 12, 2016 4:03 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL Statement length

Is there a "trick" to programming a long complex (6000 character) SQL
statement in RPG?

We keep getting a "variable truncated" error message.



[Rodda Paint Company]
Chuck Graves
Director of Information Systems
Rodda Paint Co.<http://www.roddapaint.com>
6107 N. Marine Drive
Portland, Oregon 97203
(503) 737-6042

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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.
--
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.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.


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.