Is there an Embedded SQL Cursor "rewind"?

Primary tabs

SQL, Caché

Hi -

I'm trying to rewind a cursor back to the first row after looping part of the way through the implied result set, but I'm not finding a way to make this happen, is there some such iterator variable or directive that I can leverage to accomplish this?

I could code around it by pulling identifiers and/or values into a local array, and then hand code up an iterator over my local results copy, but this feels like a "redesigning of a wheel" approach, and I thought I would check before I start down this path.

Thanks

  • 0
  • 0
  • 266
  • 1
  • 1

Answers

You can close the cursor and open it again:

ClassMethod Test()
{
     &sql(  DECLARE C1 CURSOR FOR
            SELECT TOP 10 ID
            INTO :id
            FROM Sample.Person
    )

    &sql(OPEN C1)
    &sql(FETCH C1)
    
    Set first = $$$YES

    While (SQLCODE = 0) {
        Write id,!       
        &sql(FETCH C1)
        If id=9 && first {
            Set first = $$$NO
                &sql(CLOSE C1)
                &sql(OPEN C1)
                &sql(FETCH C1)
        }
    }

    &sql(CLOSE C1)
}