RE: SQL alternative for native I/O

Repeat after me, "SQL is best for set-at-a-time operations."

Cursors, while provided, needed, and useful, are not usually the best
way to do things.  More people used to record level access, start out
with SQL by trying to duplicate the record at a time processing they are
familiar with using cursors.  However, RPG native I/O will usually
perform much better.  

What processing do you need to do?

I'd consider a solution that does the processing in a user defined
function (UDF) like so:

Update detail
Set dtlflag = MyProcess(fld1,fld2)
Where dtlflag = 'N'
 and key = (select key
            from header
            where hdrflag = 'N'
            )

Another option, would be to use a trigger to handle the required
processing for the entire set of updated detail records at once.

However, we really need to know more about the processing that needs to
be done with each detail record.

HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx 
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jake Bruster
Sent: Friday, December 15, 2006 12:35 PM
To: RPG programming on the AS400 / iSeries
Subject: SQL alternative for native I/O

What is the preferred SQL alternative to native database I/O 
in the following scenario? Read a header record and if a flag 
is on, read all the corresponding detail records for the 
header record and for those detail records whose flag is on, 
process the record and update it's flag.

Since SQL does not allow an update to a cursor for joined 
tables, do I create two cursors and update the detail record 
through its separate cursor or do I create one cursor for 
joining the two tables and update the detail record with a 
separate in-line SQL update statement or is there a better 
way to handle this type of processing using embedded SQL?

TIA,

Jake

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
-- 
This is the RPG programming on the AS400 / 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.