Re: Possible Bug with TOP N clause in cursor definition. Re: Critical difference between behaviour of @@sqlstatus vs. MS-SQL @@FETCH_STATUS?!?

Have you tried putting an ORDER BY clause in your query? That would
ensure that you get the same 10 all the time...



In article <1150315392.293501.280740@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,
emarinko@xxxxxxxxx says...
Here is some further detail about the problem I reported (see quoted
message). It seems to be a bug with the TOP N clause in the select
statement for the cursor definition.

With respect to the example below, I tried inserting the top 10
records into another table and running a cursor against that.

I noticed that if my query in the cursor definition is:

select strName from myTable

\----> Then I get 10 print statements in my cursor. (i.e. all the
expected rows).

However, if I change my query to:

select TOP 10 strName from myTable

\----> Then I only get 9 print statements out of my cursor. (last row
in result is missing).

One should expect the same result from both cases, but Sybase is not
consistent.


Riko wrote:
Hi,

That would skip the first row instead of the last row.

The first row is already fetched outside of the loop (otherwise could
never enter the loop because @@sqlstatus wouldn't be set to 0).
Then the second fetch would move to the second row before the first
print.

My concern is that my code follows the standard pattern in most
examples, including this one:

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/51747;pt=51652;lang=zh?DwebQuery=@@sqlstatus

In that case, I get no message for the last title in the result set.



David Kerber wrote:
In article <1150306713.678794.59480@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,
emarinko@xxxxxxxxx says...

...

DECLARE RC CURSOR FOR
select top 10 strName from tblCustomer

DECLARE @strName varchar(50)

OPEN RC

FETCH RC INTO @strName

WHILE @@sqlstatus=0
BEGIN
print @strName
FETCH RC INTO @strName
END

I use ASA rather than ASE so I may be off base here, but try swapping
the order of the lines inside your loop, so you fetch before printing.

...

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).



--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
.

Relevant Pages

  • If MySQL column/field values are in an PHP array
    ... I created an array using the following: ... How would I go about adding an AND clause to the above query to return all ... rows where strName column values are in $in_list array ...
    (php.general)
  • Re: OU Help Please
    ... VBScript program that uses ADO to query AD: ... Dim adoCommand, adoConnection, strBase, strFilter, strAttributes ... Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName ... ' Search entire Active Directory domain. ...
    (microsoft.public.windows.server.active_directory)
  • Re: Distinctrow
    ... SELECT strName, LAST, LAST ... just like a DISTINCT (for a SELECT query type). ... 5377 but I am still seeing information that is identical. ... Rick Brandt, Microsoft Access MVP ...
    (microsoft.public.access.queries)
  • RE: Printing query from form
    ... strName as String ... get to the database window to open and/or print a query or query results. ... parent form I've set up a button to save and open the results in an Excel ... The issue is that this opens the query in the background, ...
    (microsoft.public.access.modulesdaovba)
  • Possible Bug with TOP N clause in cursor definition. Re: Critical difference between behaviour of
    ... select TOP 10 strName from myTable ... That would skip the first row instead of the last row. ... The first row is already fetched outside of the loop (otherwise could ... Then the second fetch would move to the second row before the first ...
    (comp.databases.sybase)