Hi Roger 

Many thanks for the detailed input, I removed my computation from outside of
the loop and flipped the logic inside which actually mirrors your code below
and produces the correct result.

Richard
-----Original Message-----
From: rnedel@n... [mailto:rnedel@n...]
Sent: 15 February 2003 08:49
To: sql language
Subject: [sql_language] Re: Cursor Duplicate


Richard:

Your problem lay inside your WHILE loop.  The general methodology you 
should follow is:

  1. Declare your cursor.
  2. Open your cursor.
  3. Fetch your first record.
  4. WHILE @@FETCH_STATUS = 0
  5.   Perform your computations/print results.
  6.   Fetch your next record.
  7. END LOOP
  8. Close your cursor.
  9. Deallocate your cursor.

That is not the methodology you used.  Your algorithm is:

  1. Declare your cursor.
  2. Open your cursor.
  3. Fetch your first record.
  4. Perform your computations/print results.
  5. WHILE @@FETCH_STATUS = 0
  6.   Fetch your next record.
  7.   Perform your computations/print results again.
  8. END LOOP.
  8. Close your cursor.
  9. Deallocate your cursor.

Basically, you evaluate whether or not the fetch returned a record with 
your WHILE statement.  If it did fetch a valid record, you enter the body 
of the loop, but you then immediately attempt to fetch a brand new record 
before computing and printing the current record.

So when you fetch your last record, the fetch_status equals zero, and you 
enter the loop.  You immediately attempt to fetch the next record (which 
doesn't exist).  But you attempt to compute/print without immediately 
checking the fetch_status.  As a result, your computations and print 
statement (@NERecordCount, etc) are working on the previous (aka last) 
record.

Try the following:

Declare RangeCursor Cursor Local Forward_Only For

Select
  Convert(Char(10), tblImportControl.dtmDataRequested, 103) As Report_Date,
  tblImportControl.idsImportID As intImportID
From
  tblImportControl
Inner JOIN tblNE
  On tblImportControl.idsImportID = tblNE.intImportID
Where
  tblImportControl.tintBackedOut = 0 And
  tblNe.tintPlatform = 2
Group By
  Convert(Char(10), tblImportControl.dtmDataRequested, 103),
  tblImportControl.dtmDataRequested,
  tblImportControl.idsImportID
Order By
  tblImportControl.dtmDataRequested,
  tblImportControl.idsImportID

Open RangeCursor

Fetch Next From RangeCursor
Into @ReportDate, @ImportID

While @@Fetch_Status = 0
Begin
	
  Set @NERecordCount = dbo.NE_Processed (@ImportID,2)
  Set @CDRecordCount = dbo.CD_Processed (@ImportID,2)
  Set @RaterRecordCount = dbo.Rater_Processed (@ImportID,2)
  Set @MAFRecordCount = dbo.MAF_Processed (@ImportID,2)
  Set @GuidingRecordCount = dbo.Guiding_Processed (@ImportID,2)
  Set @RatingRecordCount = dbo.Rating_Processed (@ImportID,2)

  Print  @ReportDate + '    ' +  @ImportID + '     ' + @NERecordCount +
    '    '  +  @CDRecordCount + '    ' + @RaterRecordCount + '      ' +
    @MAFRecordCount + '      ' + @GuidingRecordCount +  '     '  +
    @RatingRecordCount

  Fetch Next From RangeCursor
  Into @ReportDate, @ImportID
	
End

Close RangeCursor
Deallocate RangeCursor
GO

Cheers.

- Roger Nedel

  Nedel Software Solutions
  rnedel@n...



 NOTICE AND DISCLAIMER:
This email (including attachments) is confidential.  If you have received
this email in error please notify the sender immediately and delete this
email from your system without copying or disseminating it or placing any
reliance upon its contents.  We cannot accept liability for any breaches of
confidence arising through use of email.  Any opinions expressed in this
email (including attachments) are those of the author and do not necessarily
reflect our opinions.  We will not accept responsibility for any commitments
made by our employees outside the scope of our business.  We do not warrant
the accuracy or completeness of such information.