Skip to Content
0

HANA Cursor order by not working

Sep 28, 2017 at 03:30 AM

32

avatar image

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?

10 |10000 characters needed characters left 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

0

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;
0
* Please Login or Register to Answer, Follow or Comment.

0 Answers