Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2010
    Posts
    4

    Unanswered: Sybase IQ Cursor Question

    I have a cursor that seems to run fine in Sybase IQ.

    However, within the cursor's fetch loop I have to update another table. When I perform the insert or the update code, the cursor loop ends. If I comment out the insert AND the update, the code runs fine (but of course, does nothing other than print out what it is doing).

    Is updating not allowed within a loop? I am NOT updating the table that is in the cursor. I'm only using the cursor to identify what needs to be updated.

    The code is below:

    create procedure sp_locktrak as
    begin
    DECLARE loktrak NO SCROLL CURSOR FOR
    SELECT encntrSID, ToUnitDTM, lvunitdtm, currlocid
    FROM crr.tmp1
    ORDER BY encntrsid, tounitdtm ASC
    FOR READ ONLY

    DECLARE
    @encntrSID INT,
    @save_encntrSID INT,
    @tounitdtm VARCHAR(20),
    @lvunitdtm VARCHAR(20),
    @location VARCHAR(20),
    @save_location varchar(20),
    @save_tounitdtm varchar(20),
    @save_lvunitdtm varchar(20),
    @first int

    select @save_location = '', @save_encntrSID = '', @first = 1

    set nocount on

    OPEN loktrak

    FETCH loktrak
    INTO @encntrSID, @tounitdtm, @lvunitdtm, @location

    while (SQLCODE = 0)
    BEGIN

    print '@encounterSID = %1!, @save_encntrSID = %2!, @location = %3!, @save_location = %4!, @@sqlstatus = %5!', @encntrSID, @save_encntrSID, @location, @save_location, SQLCODE

    if (@encntrsid = @save_encntrSID) or (@first = 1)
    if (@location != @save_location)
    begin

    insert crr.LocationHistory
    values (@encntrSID, convert(date,@tounitdtm,0), convert(date,@lvunitdtm,0), @location)

    update crr.LocationHistory
    set lvunitdtm = dateadd(mi,-1,convert(date,@save_tounitdtm,0))
    where encntrSID = @encntrSID
    and tounitdtm = convert(date,@save_tounitdtm,0)

    end

    print '@encounterSID = %1!, @save_encntrSID = %2!, @location = %3!, @save_location = %4!, @@sqlstatus = %5!', @encntrSID, @save_encntrSID, @location, @save_location, SQLCODE

    FETCH loktrak
    INTO @encntrSID, @tounitdtm, @lvunitdtm, @location
    END


    CLOSE loktrak
    end

  2. #2
    Join Date
    Aug 2010
    Posts
    19
    Cursors are evil, im most of the cases, just don´t use them :-)
    please use a Set oriented solution like :

    --------------- cut ---------------
    insert crr.LocationHistory
    SELECT encntrSID, ToUnitDTM, lvunitdtm, currlocid
    FROM crr.tmp1
    AND <IF Condition> -- eg currlocid <> ''


    update crr.LocationHistory
    set lvunitdtm = dateadd(mi,-1,convert(date,tounitdtm,0))
    from crr.LocationHistory t1
    inner join ccr.tmp t2 on t1.encntrSID = t2.encntrSID
    where t1.encntrSID = t2.encntrSID
    and t1.tounitdtm = t2.tounitdtm
    AND <IF Condition> -- eg currlocid <> ''
    --------------- cut ---------------

    if you like you can create a transaction

    begin transaction
    -- work
    commit

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •