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



One nice thing about using SQL to build subfiles is that you can do some
fancy alternate sorting by manipulating the ORDER BY clause. Done right,
this can give the user the ability to sort ascending or descending by
any column in the subfile.

Trevor Briggs
Analyst/Programmer
Lincare, Inc.
(727) 431-1246
TBriggs2@xxxxxxxxxxx

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Schutte
Sent: Tuesday, November 06, 2012 10:51 AM
To: RPG programming on the IBM i / System i
Subject: Re: Best approach for Embedded SQL SELECT in V7R1

The only problem I see with what you want to do is that you are
selecting
all fields in putting them into a data structure based on an external
file.
If the file changes you know have to recompile. The program won't
crash
but the FETCH will return an SQLCOD other than 0. Sorry don't remember
what it is.

With sub files I suggest you keep it as RLA as you have more flexibility
on
positioning to a location in a file. That's not to say that I haven't
done
it... but those were load at once sub file.

My code for MODS fetch...

D C1DS DS Occurs(100)
D Field1 7 0
D Field2 5

/free
declare c1 cursor for select fld1, fld2 from file where fld3 = 'ABC';
open c1;
fetch c1 for 100 rows into :C1ds;
DoW SqlCod <> 100 And SQLCOD >= 0;
C1CNT = SQLER3;
For iX = 1 To C1CNT;
%Occur(C1DS) = iX;
// do what ever.
EndFor;
fetch c1 for 100 rows into :C1ds;
EndDo;
close c1;


This was just thrown together, no guarantee on it's accuracy.


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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
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.