|
Matt, Do you use aggregate functions and "group by" to generate summary data? I had issues with memory corruption, where SQL sized the aggregate values too large to fit in the field defined on the fetch. No hard errors were reported, but the results were clearly in error on *some* transactions. Eric DeLong Sally Beauty Company MIS-Project Manager (BSG) 940-898-7863 or ext. 1863 -----Original Message----- From: Tyler, Matt [mailto:mattt@xxxxxxxxxxxxxx] Sent: Wednesday, November 10, 2004 6:08 PM To: 'rpg400-l@xxxxxxxxxxxx' Subject: Embedded SQL does not work all the time with a page-at-a-time SFL To all thanks for any help and apologize for the very long message. I have a SQLRPGLE program (http://archive.midrange.com/rpg400-l/200409/msg00254.html <http://archive.midrange.com/rpg400-l/200409/msg00254.html> this is a link to pertinent code excerpts) that runs on an OS400 version 5.1. I have a situation where my program will not page down to then next page of detail. I can recreate the issue but I am not sure why it happens nor how to solve. I use the same RPG logic for different SQL statements. When I format my SQL statement under certain conditions the code in subroutine will not work appropriately. The condition occurs in subroutine SQLFETCH. This subroutine is used by the page down and anywhere the screen data needs to be reloaded from the database. The program fetches the first page of data (15 rows), it checks for EOF and then sets up the page keys and MORE.../BOTTOM text. If EOF was not found after getting a full 15 rows it performs a FETCH NEXT FROM SQLCSR to position SQLCSR only and see if is at least one more row. If I find EOF then I set on indicators to ensure that BOTTOM is shown, page down will not work and make SQLCSR point to just before the last record in the query result set. Otherwise I position back up one row back to where I was after the initial fetch of 15 rows. I believe the problem lies in the statement of "FETCH PRIOR FROM SQLCSR". Under normal conditions the function returns SQLST='00000', but under this one condition the function returns SQLSTT='02000'. Debugging the program I get the following for normal conditions: 15 rows fetched from cursor SQLCSR. 0 rows fetched from cursor SQLCSR. 0 rows fetched from cursor SQLCSR. Debugging the program I get the following for the one condition: 15 rows fetched from cursor SQLCSR. 0 rows fetched from cursor SQLCSR. Row not found for SQLCSR. First call to SQLFETCH SQLCA is the following: SQLAID OF SQLCA = 'SQLCA ' SQLABC OF SQLCA = 000000136. SQLCOD OF SQLCA = 000000000. SQLERL OF SQLCA = 0022. SQLERM OF SQLCA = ....5...10...15...20...25...30...35...40...45...50...55...60 1 'SQLCSR ' 61 ' ' SQLERP OF SQLCA = 'QSQROUTE' SQLER1 OF SQLCA = 000000000. SQLERR OF SQLCA = ' À7963' SQLERRD OF SQLCA(1) = 000000000. SQLERRD OF SQLCA(2) = 000000000. SQLERRD OF SQLCA(3) = 000000015. SQLERRD OF SQLCA(4) = 000000521. SQLERRD OF SQLCA(5) = 000000000. SQLERRD OF SQLCA(6) = -134613261. SQLER2 OF SQLCA = 000000000. SQLER3 OF SQLCA = 000000015. SQLER4 OF SQLCA = 000000521. SQLER5 OF SQLCA = 000000000. SQLER6 OF SQLCA = -134613261. SQLWRN OF SQLCA = ' ' SQLWN0 OF SQLCA = ' ' SQLWN1 OF SQLCA = ' ' SQLWN2 OF SQLCA = ' ' SQLWN3 OF SQLCA = ' ' SQLWN4 OF SQLCA = ' ' SQLWN5 OF SQLCA = ' ' SQLWN6 OF SQLCA = ' ' SQLWN7 OF SQLCA = ' ' SQLWN8 OF SQLCA = ' ' SQLWN9 OF SQLCA = ' ' SQLWNA OF SQLCA = ' ' SQLSTT OF SQLCA = '00000' After paging down (once) it is: SQLAID OF SQLCA = 'SQLCA ' SQLABC OF SQLCA = 000000136. SQLCOD OF SQLCA = 000000000. SQLERL OF SQLCA = 0022. SQLERM OF SQLCA = ....5...10...15...20...25...30...35...40...45...50...55...60 1 'SQLCSR ' 61 ' ' SQLERP OF SQLCA = 'QSQROUTE' SQLER1 OF SQLCA = 000000000. SQLERR OF SQLCA = ' À7963' SQLERRD OF SQLCA(1) = 000000000. SQLERRD OF SQLCA(2) = 000000000. SQLERRD OF SQLCA(3) = 000000015. SQLERRD OF SQLCA(4) = 000000521. SQLERRD OF SQLCA(5) = 000000100. SQLERRD OF SQLCA(6) = -134613261. SQLER2 OF SQLCA = 000000000. SQLER3 OF SQLCA = 000000015. SQLER4 OF SQLCA = 000000521. SQLER5 OF SQLCA = 000000100. <- notice this SQLER6 OF SQLCA = -134613261. SQLWRN OF SQLCA = ' ' SQLWN0 OF SQLCA = ' ' SQLWN1 OF SQLCA = ' ' SQLWN2 OF SQLCA = ' ' SQLWN3 OF SQLCA = ' ' SQLWN4 OF SQLCA = ' ' SQLWN5 OF SQLCA = ' ' SQLWN6 OF SQLCA = ' ' SQLWN7 OF SQLCA = ' ' SQLWN8 OF SQLCA = ' ' SQLWN9 OF SQLCA = ' ' SQLWNA OF SQLCA = ' ' SQLSTT OF SQLCA = '00000' I can change only the order by clause values and have it start working. SQL that does not work: Select * From RIMVISQV WHERE SRVND=' 31618' AND SRUPC=2840002843 AND SRSTN IN (1,6,7,10,12,17,21,24,25,26,31,36,37,52,53,55,56,60 ) ORDER BY SRUPC FOR FETCH ONLY OPTIMIZE FOR 15 ROWS SQL that does work (a condition I am calling normal above): Select * From RIMVISQV WHERE SRVND=' 31618' AND SRUPC=2840002843 AND SRSTN IN (1,6,7,10,12,17,21,24,25,26,31,36,37,52,53,55,56,60 ) ORDER BY SRDPT, SRCAT, SRSUB, SRVND, SRUPC, SRSTN FOR FETCH ONLY OPTIMIZE FOR 15 ROWS RIMVISQV is a SQLVIEW inner joining three tables. I tried to pad out the SQL statement with 20 blanks between clauses, but that did not help. I can add a lot more store numbers to the IN condition for SRSTN and still get the same results. Thanks in advance for any help that you can provide. Thank you, Matt Tyler WinCo Foods, Inc mattt@xxxxxxxxxxxxxx -- 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.
As an Amazon Associate we earn from qualifying purchases.
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.
midrange.com
