RE: SQL Statement length

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