<table cellspacing="0" cellpadding="0" border="0" ><tr><td valign="top" style="font: inherit;"><DIV>Listers:</DIV>
<DIV> </DIV>
<DIV>Challenge solved.</DIV>
<DIV> </DIV>
<DIV>Removed GENERIC USING "@" from ACCESS statement</DIV>
<DIV> </DIV>
<DIV>created a temp T_PAT_RET CHAR*10<BR><BR>Added LET T_PAT_RET = "@" before the While retrieving</DIV>
<DIV> </DIV>
<DIV>Modified the While retrieving to "VIA PATIENT ID using T_PAT_RET"</DIV>
<DIV> </DIV>
<DIV>And that is all I needed. I also move the COMMITs from the individual Put procedures to the calling routine just before the "return" statement when exiting to the calling program.</DIV>
<DIV> </DIV>
<DIV>Thanks to all for suggestions and ideas.</DIV>
<DIV> </DIV>
<DIV>Sincerely,</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><STRONG>John M Penney</STRONG></DIV>
<DIV><STRONG>1532 Fern Street SW</STRONG></DIV>
<DIV><STRONG>Olympia, WA 98502</STRONG></DIV>
<DIV> </DIV>
<DIV><STRONG>(360)789-8159</STRONG></DIV><BR><BR>
<DIV>----- Original Message ----- </DIV>
<BLOCKQUOTE style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: rgb(16,16,255) 2px solid">
<DIV id=yiv1228667833>
<BLOCKQUOTE style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial"><B>From:</B> <A title=resolutebay@yahoo.com href="http://us.mc300.mail.yahoo.com/mc/compose?to=resolutebay@yahoo.com" target=_blank rel=nofollow ymailto="mailto:resolutebay@yahoo.com">Penney, John</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=powerh-l@lists.sowder.com href="http://us.mc300.mail.yahoo.com/mc/compose?to=powerh-l@lists.sowder.com" target=_blank rel=nofollow ymailto="mailto:powerh-l@lists.sowder.com">power-l</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Friday, February 26, 2010 12:20 AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Losing a Read Chain pointer in a While Retrieving</DIV>
<DIV><BR></DIV>
<TABLE cellSpacing=0 cellPadding=0 border=0>
<TBODY>
<TR>
<TD vAlign=top>
<DIV>Dear Listers:</DIV>
<DIV> </DIV>
<DIV>I am losing somehow a read chain within a while-retrieving loop- the first row gets processed, I do some updates, then it's off to la-la land and the program exits, while there are more records on the input table to process.. Doh! </DIV>
<DIV> </DIV>
<DIV>Will freely admit that I cribbed a whole whack of code because of the need to get this out....And also state that the "TRANSACTION" and some of the syntax is not exactly a familiar path.</DIV>
<DIV> </DIV>
<DIV>I have the following code:</DIV>
<DIV> </DIV><FONT face=r_ansi size=2><FONT face=r_ansi size=2>
<DIV>SCREEN EXE:CTS_XXXXXXX &</DIV>
<DIV>NOMODE FROM 22,1 TO 24,80 &</DIV>
<DIV>ACTION LABEL &</DIV>
<DIV>"CTS_XXXXXXX Error. Press [PF4]/[PgUp] to Continue" &</DIV>
<DIV>AT 3,10 & </DIV>
<DIV>TRANSACTION MODEL CONCURRENCY</DIV></FONT></FONT>
<DIV> </DIV><FONT face=r_ansi size=2><FONT face=r_ansi size=2>
<DIV>TRANSACTION READ_ONLY READ ONLY READ COMMITTED</DIV>
<DIV></FONT></FONT><BR><FONT face=r_ansi size=2><FONT face=r_ansi size=2> </DIV>
<DIV>FILE ESPS_XXXX IN GLBL_DATABASE DESIGNER &</DIV>
<DIV>TRANSACTION READ_ONLY FOR QUERY,PROCESS,UPDATE</DIV>
<DIV>ACCESS VIA PATIENT_ID GENERIC USING "@"</DIV>
<DIV></FONT></FONT><FONT face=r_ansi size=2><FONT face=r_ansi size=2> </DIV>
<DIV>FILE ESPS_YYYY_ERRORS DESIGNER &</DIV>
<DIV>TRANSACTION TRANS_WRITE FOR UPDATE</DIV>
<DIV> </DIV>
<DIV>FILE ZZZZ IN GLBL_DATABASE PRIMARY &</DIV>
<DIV>TRANSACTION UPDATE FOR QUERY,PROCESS,UPDATE</DIV>
<DIV>ACCESS VIA PATIENT_ID </DIV></FONT></FONT>
<DIV><STRONG></STRONG> </DIV>
<DIV><STRONG><FONT face=r_ansi size=2><FONT face=r_ansi size=2>
<DIV>FILE AAAA_EVAL_DATA_HEMO IN GLBL_DATABASE SECONDARY NOITEMS &</DIV>
<DIV>TRANSACTION UPDATE FOR QUERY,PROCESS,UPDATE</DIV>
<DIV>ACCESS VIA PATIENT_ID USING PATIENT_ID OF ZZZZ </DIV>
<DIV> </DIV>
<DIV>FILE ZZZZ_HST IN GLBL_DATABASE ALIAS HST_PAT_NEW SECONDARY &</DIV>
<DIV>TRANSACTION UPDATE FOR QUERY,PROCESS,UPDATE</DIV></FONT></FONT></STRONG></DIV>
<DIV><STRONG></STRONG> </DIV>
<DIV><STRONG>Procedure code is:</STRONG></DIV>
<DIV><STRONG></STRONG> </DIV>
<DIV><STRONG></STRONG> </DIV>
<DIV><STRONG></STRONG> </DIV>
<DIV><STRONG></STRONG> </DIV>
<DIV><FONT face=r_ansi size=2><FONT face=r_ansi size=2>
<DIV>PROCEDURE INITIALIZE</DIV>
<DIV> </DIV>
<DIV>BEGIN</DIV>
<DIV>ROLLBACK TRANSACTION QUERY</DIV>
<DIV>ROLLBACK TRANSACTION UPDATE</DIV>
<DIV> </DIV>
<DIV>WHILE RETRIEVING ESPS_XXXX</DIV>
<DIV> </DIV>
<DIV>BEGIN</DIV>
<DIV>LET T_RECINP = T_RECINP + 1</DIV>
<DIV>DO INTERNAL PROCESS_PART_ROWS</DIV>
<DIV>END </DIV>
<DIV>;;</DIV>
<DIV>;; Output Program Statistics: </DIV>
<DIV>;;</DIV>
<DIV>;; This Procedure writes a Statistics report of rows read from ESPS_XXXX</DIV>
<DIV>;; Table and processed/rejected:</DIV>
<DIV>;;</DIV></FONT></FONT></DIV>
<DIV><STRONG></STRONG> </DIV>
<DIV><STRONG>Everything works like the proverbial charm for the first row- all updates and commits take place in INTERNAL PROCESS_PART_ROWS, everybody's apparently happy, but using DEBUG it just goes to sleep after retrieving that first row on ESPS_XXXX....I do 3 "PUT's" and a commit, and zzzzzz. Exit to OS.</STRONG></DIV>
<DIV><STRONG></STRONG> </DIV>
<DIV><STRONG>Box=HP Alpha</STRONG></DIV>
<DIV><STRONG></STRONG> </DIV>
<DIV><STRONG>PH=<FONT face=r_ansi size=2><FONT face=r_ansi size=2>QDESIGN710G </FONT></FONT></STRONG></DIV>
<DIV><STRONG><FONT face=r_ansi></FONT></STRONG> </DIV>
<DIV><STRONG><FONT face=r_ansi>Rdb:<FONT face=r_ansi size=2><FONT face=r_ansi size=2>Oracle Rdb SQL V7.2-351</FONT></FONT></FONT></STRONG></DIV>
<DIV><STRONG></STRONG> </DIV>
<DIV><STRONG>I can think of workarounds, but this shouldn't be rocket science, eh?</STRONG></DIV>
<DIV><STRONG></STRONG> </DIV>
<DIV><STRONG>Tell me it ain't so..</STRONG></DIV>
<DIV><STRONG></STRONG> </DIV>
<DIV><STRONG>Go Canada Go!</STRONG></DIV>
<DIV><STRONG></STRONG> </DIV>
<DIV><STRONG>John M Penney</STRONG></DIV>
<DIV><STRONG>1532 Fern Street SW</STRONG></DIV>
<DIV><STRONG>Olympia, WA 98502</STRONG></DIV>
<DIV> </DIV>
<DIV><STRONG>(360)789-8159</STRONG></DIV></TD></TR></TBODY></TABLE><BR>
<DIV>
<HR>
<DIV></DIV>-- <BR>= = = = = = = = = = = = = = = = = = = = = = = = = = = =<BR>Mailing list: powerh-l@lists.sowder.com<BR>Subscribe: 'subscribe' in message body to powerh-l-request@lists.sowder.com<BR>Unsubscribe: 'unsubscribe &lt;password&gt;' in message body to powerh-l-request@lists.sowder.com<BR>http://lists.sowder.com/mailman/listinfo/powerh-l<BR>This list is closed, thus to post to the list you must be a subscriber.<BR>Add 'site:lists.sowder.com powerh-l' to your search terms to search the list archive at Google.</BLOCKQUOTE></DIV></BLOCKQUOTE></td></tr></table><br>