Tweet
I like to use SQL when doing page at a time subfiles because I find it very easy to use. I use 'FETCH RELATIVE' and keep track of when I am at top or bottom of the file so I don't rebuild the subfile when I know the operation will only return empty record set. I use a negative number on the fetch to page backward and a positive number to page forward. Ex: FETCH RELATIVE :SCROLL FROM C1 INTO :SQLCO The host variable SCROLL contains the number of records to go forward or backward to start the Fetch. I use 1 to go forward and to go backward I use the number of records on a page in my subfile times 2.
-
-
SQL- Whenever Not Found
Robert, Thanks for your help and time. Before loading i have to validate the data. So, the qualified data only need to be loaded. I can't use FETCH RELATIVE. Anyway, my problem is solved. I've removed WHENEVER condition and used COUNT(*). If the result set is = 0. Iam displaying a No list. Regards, Saravanan -
SQL- Whenever Not Found
Are you not able to put the data qualifications in a WHERE clause? If this is true then how can COUNT(*) use your data qualification? Perhaps I can learn something here.Comment
-
SQL- Whenever Not Found
#1) I can put data qualification in WHERE clause, but i have a CMD Key07 for toggle between active and other records. For this i need to have two select statements with two different WHERE clauses and obviously 2 cursors. I think this would make things complicated. So iam simply selecting all records and validating the data before loading. 2#) I used COUNT(*) instead WHENEVER not Found, not for data validation. While looking for the solutions for my problem, found the following regarding WHENEVER. " 1) A given WHENEVER is active until another WHENEVER is encountered in the source - regardless of subroutine structure. Very error-prone! 2) Cannot easily return to where you came from. Even though it seems like more work, I think it's definitely worth coding explicit checks for SQLCOD/SQLWN0 or SQLSTT. It also makes debugging easier." Rgds, Saravanan.Comment
-
SQL- Whenever Not Found
I agree with you concerning the WHENEVER statement. I prefer to examine the sqlcod myself. Have you ever used a CASE Statement in a WHERE Clause? It eliminates the need for multiple cursors and I find it works quite well. Here is an example: C/EXEC SQL C+ DECLARE C1 SCROLL CURSOR FOR C+ SELECT BCODE,BCO,BOPER#,BNAME C+ FROM MSTFILB C+ WHERE ( CASE WHEN :Key07 = '1' THEN Active C+ WHEN :Key07 = '0' THEN Active C+ END ) = C+ ( CASE WHEN :Key07 = '1' THEN 'Y' C+ WHEN :Key07 = '0' THEN 'N' C+ END ) C/END-EXEC The above WHERE clause changes it criteria base upon the value in Key07. When Key07 = '1' the WHERE Clause will evaluate this: Where Active = 'Y' When Key07 = '0' it evaluates this: Where Active = 'N' You will notice the CASE statements are on both sides of the equal sign. This has saved me from having to write multiple cursors in one program and works quit well. I only mention this so you will have another tool in your arsenal for programs in the future. I don't suggest you change your current program which you already have running. Just keep this technique in mind for the future.Comment
-
SQL- Whenever Not Found
The last response I sent you concerning using CASE statements in your Where clause was accurate but a poor example. You would use a CASE stmt when you want to change the columns being checked and not necessarily the value of that column. A simpler Where clause which does the same thing would be: Where :Key07 = Active Assuming Active is a one byte column in your table with '1' meaning active and '0' meaning not active. I just wanted to clear up the CASE stmt example of mine.Comment
-
SQL- Whenever Not Found
Hi All, Iam loading a page subfile with SQL result set. Say, there are only two pages to load. If i do the pageup and pagedown continously, every 3rd time, the SQL Whenever not found condition is met and it is displaying the empty subfile. Immediately if do a Pageup or a pagedown, it is showing the subfile with records. I.e., If the empty sfl is shown while pageup, if i do a pageup again it is displying the subfile with records. Iam keeping track of the page numbers in my program, if the page = 1, iam doing a Fetch BEFORE. So that the pointer would be set before the first records of the result set. Even i tried CLOSING and OPENING the CURSOR again still the problem persists. What could be the problem? Any help is highly appreciated. Thanks, Saravanan.Comment
-
SQL- Whenever Not Found
Hi Robert, I never knew this before. Thanks for that valuable TIP. Rdgs, Saravanan.Comment
Comment