Skip to Content

HANA Cursor order by not working

Hi, all,

I have to use cursor to iterate over all the rows in a certain order. But after I implement it with order by, the result is not as expected. I logged the result with ::rowcount, and noticed the order by is not really working.

I have tried to generate row number in the dataset, and then loop through the rows by row number, it will work in this way. But it takes significantly longer time than cursor.

Do you know if using order by with cursor is really supported in HANA?

Add comment
10|10000 characters needed characters exceeded

  • Order by is supported also by cursors. Can you share your code (e.g. did you use a fetch or a For loop to retrieve the results)?

    Regards,
    Florian

  • I tried both.This is the one using fetch.

    do begin 
    DECLARE LV_MIN_MVI_DT Date :=TO_date('19000101','YYYYMMDD');
    DECLARE LV_COUNT INTEGER :=0;
    DECLARE LV_COUNTER INTEGER :=1;
    DECLARE LV_BP_NUM NVARCHAR(10) :='';
    DECLARE LV_BP_ID NVARCHAR(10) :='';
    DECLARE LV_MVI_DT DATE;
    DECLARE LV_MVO_DT DATE;
    
    
    declare cursor cursor1 for select business_partner_id,min_mvi_dt,max_mvo_dt from XXXX.TMP_BP_CONT_GAP_RANGE2
    order by business_partner_id,max_mvo_dt desc;
    	
    create local temporary column table XXXX.#TMP_CONTRACT_AGE2(
    BUSINESS_PARTNER_ID NVARCHAR(10),
    START_DT DATE,
    ROW_NUM INTEGER
    );
    SELECT count(*) into LV_COUNT FROM XXXX.TMP_BP_CONT_GAP_RANGE2;
    
    
    OPEN cursor1;
    fetch cursor1 into LV_BP_ID,LV_MVI_DT,LV_MVO_DT;
    WHILE NOT cursor1::NOTFOUND DO	
    	   IF :LV_BP_ID != :LV_BP_NUM 
    		THEN 
    		    IF cursor1::ROWCOUNT != 1  
    		    THEN 
    		    INSERT INTO XXXX.#TMP_CONTRACT_AGE2 values(:LV_BP_NUM,:LV_MIN_MVI_DT,cursor1::ROWCOUNT);
    		    END IF;		    
    		    LV_BP_NUM := :LV_BP_ID;
    		    LV_MIN_MVI_DT := :LV_MVI_DT;		
    		ELSE 
    		    IF :LV_MVI_DT <:LV_MIN_MVI_DT and  LV_MVO_DT >= add_days(:LV_MIN_MVI_DT,-30)
    		    then 
    		        LV_MIN_MVI_DT := :LV_MVI_DT;
    		    End If;
    		END IF;		
    		IF cursor1::ROWCOUNT = :LV_COUNT  
    		    THEN 
    		    INSERT INTO XXXX.#TMP_CONTRACT_AGE2 values(:LV_BP_NUM,:LV_MIN_MVI_DT,cursor1::ROWCOUNT);
    		    END IF;
    fetch cursor1 into LV_BP_ID,LV_MVI_DT,LV_MVO_DT;
    END While;
    close cursor1;
    end;
    
  • Get RSS Feed

0 Answers