Re: Best approach for Embedded SQL SELECT in V7R1

Yes.

But the WHERE clause would be used if you were trying to offer "position
to" also...

When I say "subset by" (aka "Filter by") I'm really talking about a
mindset...for both the developer and the users.

Consider that with RLA and "position to", you've basically got one or more
keyed logicals your user can use for positioning. But even if you have 3
or 4, the user can only use one at a time. SO for example, your customer
maint program might allow you to display and position to by either customer
number or customer name.

With subset by, an user could ask for customers in ('OH','IN','WV') whose
names contain the word "Acme".

Charles


On Tue, Nov 6, 2012 at 10:42 AM, Jeff Young <jyoung0950@xxxxxxxxx> wrote:

Charles,
What is the "subset by" that you refered to in your reply.
Is this the Where clause on the Select?

Thanks,

On Tue, Nov 6, 2012 at 10:20 AM, Charles Wilt <charles.wilt@xxxxxxxxx

wrote:

As far as I recall, nothing has been added to 7.1 that helps here...so

the

following applies from at least v5r4 forward.

Yes, if you want to see any performance improvement in SQL vs. RLA,

you'll

need to fetch multiple records. Either into a MODS or an array. I

prefer

array's and IIRC v5 somthing is where IBM added support for fetching into
them.

Here's the key: SQL doesn't support the idea of "position to" like RLA
does. For example, with RLA, I can position to a key value (say '123',
then page backward or forward. To do this via SQL requires either two
cursors (WHERE >= '123', WHERE < '123') or closing reopening the cursor.

With SQL you're better off with using "subset by". The resulting serahc
functionality can be considerably more flexible and powerful (think RegEx
for example); but your users may need some time and training to accept

it.

HTH,
CHarles


On Tue, Nov 6, 2012 at 9:37 AM, Michael Ryan <michaelrtr@xxxxxxxxx>

wrote:

Hi folks...looking for opinions/ideas here. I'm working with a program

that

declares a cursor, then does a FETCH NEXT to iterate through the result

set

and fill a subfile. It looks like this:

DECLARE C1 CURSOR FOR
Select * From Inventory
Where...<where and order by clauses>
...
Exec SQL
Fetch Next
From C1
Into :WkInventory;

Where WkInventory is an externally described DS based on the Inventory
file. The data is then moved from WkInventory to the subfile, and the
subfile is eventually displayed.

I *think* that performance would improve by FETCHing multiple rows

into a

MODS or an array in a data structure, rather than esentially performing

RLA

with SQL. Recently upgraded to V7R1, so I'm looking for the best

approach

at this OS level - I know I can do more than I could do in V5R4. My

first

thought is a MODS and specifying a number on the FETCH. But then I need

to

be concerned about 're-FETCHing' when I run out of rows for my subfile,
right? Would an array make more sense than a MODS?

I want to use SQL where it makes sense and RLA where it makes sense. I

need

to work through this concept so I can use this technique as I go

forward.

Ideas, opinions, code samples all welcome. Thanks in advance!
--
This is the RPG programming on the IBM i / System i (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 / System i (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.

--
Jeff Young
Sr. Programmer Analyst
--
This is the RPG programming on the IBM i / System i (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.