Re: Multiple INCLUDE statements in SQLRPGLE

My thanks to all who responded.

The copy books contain common code used in more than 50 places. It's not
confusing when you're consistent and I'm very consistent! If I have to
change the core components of the SQL, I'd like to do it once, in one
place. WRT "confusing"--absolutely not! I'm merely following best
practices by reusing code (it just happens to be source, not object).
Every time you reference an externally-defined file or execute a CALL,
you're reusing code. The fact the reuse is by the compiler is irrelevant.
But nested INCLUDES wouldn't be something I'd try--that could make the code
harder to navigate. EXEC IMMEDIATE isn't an option because I'm running
multiple queries for every transaction.

The structure of the data is a collection of ZIP codes grouped together and
referred to by a name (key). Lower Manhattan is covered by ZIPs
10004-10007, 10038, and 10280, so that name might be LMANNY. The SP
accepts a ZIP code and candidate name and returns "true" if the ZIP code
submitted is contained within the candidate name. SPS010('LMANNY','10007)
= '1'; SPS010('LMANNY','10008') = '0'. The name is a pure synonym; a
natural name won't work because there are so many weird combinations set up
by the business users. This means I have to hit this logic often,
depending on how the users have configured the data. This approach has
enabled significant productivity increases but with more than 25,000
combinations we are now paying the price. So, if I have two names each
referencing five ZIP codes, the users can save a lot of time and effort by
making one entry--otherwise, they'd have to set up a matrix of 25 entries.
But this approach hasn't been ruled out: pricing is imported from a
spreadsheet and all I'd have to do is explode the names into their
component ZIP codes, thereby flattening the database and providing
excellent throughput when the appropriate indexes are built. A problem is
that the users think of LMANNY, not the individual six ZIP codes, and if a
new ZIP code were added to lower Manhattan, all I have to do is update
LMANNY.

The original logic used an SQL stored procedure. But the nature of this
extremely complex pricing application caused that SP to be called millions
(as counted by performance tools) of times a day. A major increase in the
number of records in the database caused performance to tank so I swapped
out the SQL SP for a service program SP. That helped a bit but I still had
a problem so I tested a WHERE EXISTS clause in an attempt to push as much
of the selection load as possible into the database engine. While the
performance in that approach was substantially better, I wondered if I
could improve a bit more.

My current approach is to flatten the select statement by using a JOIN and
hoping the Index Advisor will help me build supporting indexes. I'm
already using an INCLUDE copy book with most of the select statement. But
there are some situations where I need to insert the JOIN and that's why
I'm looking into multiple SQL copy books--so I can insert a JOIN between
SELECT and WHERE when necessary. The less-than-pure approach is to
duplicate the copy book I'm using, add the JOIN, and use the "with join" or
"without join" as appropriate.

Stay safe-

-reeve

On Sat, May 9, 2020 at 1:48 PM Carel <coteijgeler@xxxxxxxxx> wrote:

Why not have the SQL statmements in a source file, read through that
file to build the statement and then do a EXEC IMMEDIATE?


Op 9-5-2020 om 21:52 schreef Brian Parkins:

You say, "The goal is to move common code out of the application
source and into copy books for ease of development and maintenance."

May I humbly suggest that your (laudable) goal precludes the use of
copybooks in this way. In fact, you may end up with something more
difficult to maintain.

Have you considered wrapping your common code in procedures and
packaging these in a service program?

Brian.

On 09/05/2020 07:17, Reeve wrote:

I'm trying to stack multiple INCLUDE's to generate static SQL. The
goal is
to move common code out of the application source and into copy books
for
ease of development and maintenance. I have a need to include a JOIN in
some cases, and while I could simply have two versions of a single
consolidated copy book (one with the join and one without), that
solution
doesn't appeal to me.

This is the first copy book:
EXEC SQL
SELECT cmcust INTO :cmcust FROM arp001

and this is the second:
WHERE cmstat = 'A'

and this is the third:
AND CMCLS = 'R'

...and there will be additional selections.

Here's what the code might look like:
/include qcpysrc,testcopy1
JOIN arp002 ON cmcust = nmcust
/include qcpysrc,testcopy2
/include qcpysrc,testcopy3
and cmcls = 'R' and nmtype = '2';

The source out of the preprocessor includes an /END-FREE and the
compiler
tags the "-" in column 11 as an error even when the source member
includes
a /FREE in it. The first SQL line isn't terminated so the
preprocessor is
likely not looking for an /END-FREE.
Position 11 Token - was not valid. Valid tokens: FOR USE

When I've added a /FREE statement to these source members, the error
still
appears. I've tried COPY as well; still getting errors in the
preprocessor
output. Using RPGPPOPT(*LVL2) and V7R3M0.

I'll be grateful for any ideas!

Stay safe and stay healthy.

-reeve

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com