Re: 4GL/Engine: FETCH RELATIVE acting flakey

I, Jacob Salomon, requested help figuring out why the first row of a cursor kept coming up twice while I was unable to replicate this in a simpler query. It's amazing what a weekend away from your problems can contibute toward their solutions! And it was so simple and stupid! Almost as dumb (and subtle) as a comment [in C] not closed where you were supposed to. To compound it, my SQL test was flawed by being too smart! My redeeming argument is that I withdrew my claim of a product bug after my 4gl test failed to replicate it. OK, here is the answer, accompanied by all manner of self flagellation. My remark: > - The first row of data is really duplicated > No, I ran SQL on the query. Exactly 2 rows in the active set. was wrong! I was running a query on a join of a master-detail pair, but only retrieving the primary key of the master table. The first master record happens to have two detail rows, hence the repeat of that first primary key value. So why did the select count return the number 2? Because (stupid &^%!) my fingers typed the "select count (unique pk_column)" for the select count. I had neglected to use that clause - UNIQUE - in the select statement for the primary key data. But I kept seeing it somehow when I eyeballed over my code. That's Freudian, isn't it? ;-) I am posting this (as opposed to simply canceling my post) to keep others from continuing to speculate on possible causes, as well as for general knowlege. I have found that I am seldom alone in the most bizarre problems (and believe me, I've run into some lulus); this may help someone else in a similar bind. Thanks to Nils, Dan, and Bret for their efforts in narrowing my focus on where my logic went south. Extra thanks to Bret for your encouraging words. -- -- Jake (Coding too much logic around chickens) Alternative .sig: The logic is willing; the fingers were unable . . _..-'( )`-.._ ./'. '||\\\\. }\\_/{ .//||` .`\\. ./'.|'.'||||\\\\|.. )o o( ..|//||||`.`|.`\\. ./'..|'.|| |||||\\`````` \\,@,/ ''''''/||||| ||.`|..`\\. ./'.||'.|||| ||||||||||||. ||| .|||||||||||| ||||.`||.`\\. /'|||'.|||||| ||||||||||||{ | }|||||||||||| ||||||.`|||`\\ '.|||'.||||||| ||||||||||||{ | }|||||||||||| |||||||.`|||.` '.||| ||||||||| |/' ``\\||`` | ''||/'' `\\| ||||||||| |||.` |/' \\./' `\\./ \\!|\\ /|!/ \\./' `\\./ `\\| V V V }' `\\ /' `{ V V V \\ \\ \\ V / / / +-----------------------------------------------------------+ | Impeccable Logic: A thought process which successfully | | resists chicken bites | +-----------------------------------------------------------+ > Hi family, > > I am having a flaky problem with the FETCH command. I am writing a > classical menu-driven program, with QUERY, NEXT, PREVIOUS menu items. > > When the user calls for QUERY, I initiate the SELECT, declare a cursor > and issue the FETCH FIRST command. When the user presses "NEXT", I > issue the command: FETCH RELATIVE nn cursor_name. Here is a code > snippet: > > function fetch_libdoc(nfetch) > define nfetch smallint > define nparm smallint # Parameter counter > > if nfetch = 0 > then > fetch first libdoc_pk_curs # Effective fetch first > into rp_libdoc.funcname > else > fetch relative nfetch libdoc_pk_curs # Skip to current + > into rp_libdoc.funcname # nfetch rows > end if > > Problem: The first time I issue the FETCH RELATIVE command, it > retrieves the first row of the query all over again! A "select count" > retrieves 2 yet I can successfully run a total of 3 fetches. The form > comes up with [document 3 of 2], clearly ridiculous. > > Answers I have eliminated: > - nfetch is 0 or null > No, I have repeatedly checked this in the debugger before the fetch. > > - I have closed the cursor and reopened it somewhere. > Not possible; it would have aborted the next time I did a fetch. > Just in case, I just grep'ed for the cursor name. My code has not > closed the cursor anywhere. > > - The first row of data is really duplicated > No, I ran SQL on the query. Exactly 2 rows in the active set. > > I have also tried using an unqualified FETCH and FETCH ABSOLUTE 1 for my > initial fetch command. The problem persists! > > Thinking I have found a silly bug, I wrote a small test program to > demonstrate the problem. SURPRISE! I could not replicate it! > My tester issues FETCH FIRST followed by a loop of FETCH RELATIVE. All > data was retrieved as appropriate. > > OK, I've eliminated the obvious. What could I possibly be overlooking? > I need a brainstorming session! > > Thanks for any help. > -- > -- Jake (With a talent for finding showstoppers)