#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    3
    Rep Power
    0

    Need help with a cursor


    I am trying to allocate a lump sum payment to multiple fee amounts. Here is a cursor I started writing but I can get it to work. My syntax is probably wrong and I am not sure how to use the IF close....Any help is appreciated.

    Code:
    DECLARE 
    @V_FEE_AMT DECIMAL(14,2) = 0.0,
    @PAYMENT_AMT DECIMAL(14,2) = 0.0,
    @V_BAL DECIMAL(14,2) = 0.0;
    
    DECLARE L CURSOR FOR SELECT FEE_ITEM_AMOUNT, PAYMENT_AMOUNT FROM AATABLE_PERMIT_FEE_PS WHERE PERMITNUM= 'B-0509457-0' AND FEE_ITEM_AMOUNT > 0 ORDER BY FEE_ITEM_AMOUNT DESC;
    OPEN L
    
    FETCH NEXT FROM L INTO @V_FEE_AMT, @PAYMENT_AMT;
    
    WHILE @@FETCH_STATUS = 0
    	BEGIN
    		IF @PAYMENT_AMT > @V_FEE_AMT AND @V_BAL = 0   
    			UPDATE AATABLE_PERMIT_FEEALLOCATION_PS SET PAYMENT_ALLOCATION =  @V_FEE_AMT;
    			SET @V_BAL = @PAYMENT_AMT - @V_FEE_AMT;
    		IF
    			UPDATE AATABLE_PERMIT_FEEALLOCATION_PS SET PAYMENT_ALLOCATION = @V_FEE_AMT
    			SET @V_BAL = @V_BAL - @V_FEE_AMT;
    	END
    CLOSE L
    DEALLOCATE L
    GO
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2015
    Posts
    1
    Rep Power
    0
    I think this change will help u

    Code:
    DECLARE 
     @V_FEE_AMT DECIMAL(14,2) = 0.0,
     @PAYMENT_AMT DECIMAL(14,2) = 0.0,
     @V_BAL DECIMAL(14,2) = 0.0;
    
     DECLARE L CURSOR FOR SELECT FEE_ITEM_AMOUNT, PAYMENT_AMOUNT FROM AATABLE_PERMIT_FEE_PS WHERE PERMITNUM= 'B-0509457-0' AND FEE_ITEM_AMOUNT > 0 ORDER BY FEE_ITEM_AMOUNT DESC;
     OPEN L
    
     FETCH NEXT FROM L INTO @V_FEE_AMT, @PAYMENT_AMT;
    
     WHILE @@FETCH_STATUS = 0
     BEGIN
     IF @PAYMENT_AMT > @V_FEE_AMT AND @V_BAL = 0 
     BEGIN
     SET @V_BAL = @PAYMENT_AMT - @V_FEE_AMT;
     UPDATE AATABLE_PERMIT_FEEALLOCATION_PS SET PAYMENT_ALLOCATION = @V_FEE_AMT,@V_BAL = @V_BAL - @V_FEE_AMT;
     
     END
    	
     END
     CLOSE L
     DEALLOCATE L
     GO

IMN logo majestic logo threadwatch logo seochat tools logo