[Help] - SQL to SAP HANA migration

[Former Member]

Hi guys, I need some help on migrating our script from SQL to SAP HANA. I'm stuck with "CURSOR"

DECLARE curTemp CURSOR FOR SELECT LineType,_LineNum, _LineSeq FROM #TempTab ORDER BY DocEntry, _LineNum, _LineSeq


SET @TotalLevel = 0
OPEN curTemp
FETCH curTemp INTO @LineType, @_LineNum, @_LineSeq
	WHILE @@FETCH_STATUS = 0
	BEGIN
			IF (@LineType = 'S')
			BEGIN
				UPDATE #TempTab SET TotalLevel = @TotalLevel WHERE _LineNum = @_LineNum AND _LineSeq = @_LineSeq
				SET @TotalLevel = @TotalLevel + 1
			END
			ELSE 
			IF (@LineType = 'R')
			BEGIN
				SET @TotalLevel = 0
			END
		FETCH curTemp INTO @LineType, @_LineNum, @_LineSeq
	END
CLOSE curTemp
DEALLOCATE curTemp

VS

DECLARE CURSOR curTemp FOR SELECT "LineType", "_LineNum", "_LineSeq" FROM TempTab ORDER BY "DocEntry", "_LineNum", "_LineSeq";
  
TotalLevel:= 0;
FOR cur_row as curTemp DO
	IF cur_row = 'S' THEN
		SELECT * FROM TempTab;
	END IF;
END FOR; 

I'm using stored procedure. In the middle of script, I need to declare cursor. But SAP HANA is not accepting it. The declaration should be in the top of the script.

Below is my whole SAP HANA script:

DROP PROCEDURE "spAppMarketingDoc";


CREATE PROCEDURE "spAppMarketingDoc"(in DocKey integer, in ObjectId integer)
as


BEGIN
 
DECLARE doc_1 nvarchar(2000); 
DECLARE object NVARCHAR(4000);
DECLARE LineSeq int;
DECLARE _LineNum int;
DECLARE _LineSeq int;	
DECLARE LineType nchar(1);
DECLARE TotalLevel int;


IF (:ObjectId=13) THEN object:= 'INV';
	ELSEIF (:ObjectId=14) THEN object:= 'RIN';
	ELSEIF (:ObjectId=15) THEN object:= 'DLN';
	ELSEIF (:ObjectId=16) THEN object:= 'RDN';
	ELSEIF (:ObjectId=17) THEN object:= 'RDR';
	ELSEIF (:ObjectId=18) THEN object:= 'PCH';
	ELSEIF (:ObjectId=19) THEN object:= 'RPC';
	ELSEIF (:ObjectId=20) THEN object:= 'PDN';
	ELSEIF (:ObjectId=21) THEN object:= 'RPD';
	ELSEIF (:ObjectId=22) THEN object:= 'POR';
	ELSEIF (:ObjectId=23) THEN object:= 'QUT';
	ELSEIF (:ObjectId=24) THEN object:= 'RCT';
	ELSEIF (:ObjectId=25) THEN object:= 'DPS';
	ELSEIF (:ObjectId=46) THEN object:= 'VPM';
	ELSEIF (:ObjectId=58) THEN object:= 'INM';
	ELSEIF (:ObjectId=59) THEN object:= 'IGN';
	ELSEIF (:ObjectId=60) THEN object:= 'IGE';
	ELSEIF (:ObjectId=67) THEN object:= 'WTR';
	ELSEIF (:ObjectId=68) THEN object:= 'WKO';
	ELSEIF (:ObjectId=69) THEN object:= 'IPF';
	ELSEIF (:ObjectId=112) THEN object:= 'DRF';
	ELSEIF (:ObjectId=132) THEN object:= 'CIN';
	ELSEIF (:ObjectId=140) THEN object:= 'PDF';
	ELSEIF (:ObjectId=162) THEN object:= 'MRV';
	ELSEIF (:ObjectId=163) THEN object:= 'CPI';
	ELSEIF (:ObjectId=164) THEN object:= 'CPV';
	ELSEIF (:ObjectId=165) THEN object:= 'CSI';
	ELSEIF (:ObjectId=166) THEN object:= 'CSV';
	ELSEIF (:ObjectId=191) THEN object:= 'SCL';
	ELSEIF (:ObjectId=198) THEN object:= 'FCT';
	ELSEIF (:ObjectId=199) THEN object:= 'MSN';
	ELSEIF (:ObjectId=202) THEN object:= 'WOR';
	ELSEIF (:ObjectId=203) THEN object:= 'DPI';
	ELSEIF (:ObjectId=204) THEN object:= 'DPO';
	ELSEIF (:ObjectId=321) THEN object:= 'ITR';
	ELSEIF (:ObjectId=140000009) THEN object:= 'OEI';
	ELSEIF (:ObjectId=140000010) THEN object:= 'IEI';
	ELSEIF (:ObjectId=540000006) THEN object:= 'PQT';
	ELSEIF (:ObjectId=1470000113) THEN object:= 'PRQ';
	ELSEIF (:ObjectId=1250000001) THEN object:= 'WTQ';
END IF;


/*Create temp table*/
CREATE GLOBAL TEMPORARY TABLE DOC AS
	(
		SELECT * FROM "ORIN" WHERE 1 = 2 
	);
CREATE GLOBAL TEMPORARY TABLE DOC1 AS
	(
		SELECT * FROM "RIN1" WHERE 1 = 2 
	);	
CREATE GLOBAL TEMPORARY TABLE DOC3 AS
	(
		SELECT * FROM "RIN3" WHERE 1 = 2 
	);
CREATE GLOBAL TEMPORARY TABLE DOC5 AS
	(
		SELECT * FROM "RIN5" WHERE 1 = 2 
	);
CREATE GLOBAL TEMPORARY TABLE DOC9 AS
	(
		SELECT * FROM "RIN9" WHERE 1 = 2 
	);
CREATE GLOBAL TEMPORARY TABLE DOC10 AS
	(
		SELECT * FROM "RIN10" WHERE 1 = 2 
	);	
 
/*Insert to temp table*/				
doc_1:= 'INSERT INTO DOC (SELECT * FROM "O'||:object||'" WHERE "DocEntry" = '||:DocKey||')'; 
EXEC (:doc_1);
doc_1:= 'INSERT INTO DOC1 (SELECT * FROM "'||:object||'1" WHERE "DocEntry" = '||:DocKey||')'; 
EXEC (:doc_1);
doc_1:= 'INSERT INTO DOC3 (SELECT * FROM "'||:object||'3" WHERE "DocEntry" = '||:DocKey||')'; 
EXEC (:doc_1);
doc_1:= 'INSERT INTO DOC5 (SELECT * FROM "'||:object||'5" WHERE "AbsEntry" = '||:DocKey||')'; 
EXEC (:doc_1);
doc_1:= 'INSERT INTO DOC9 (SELECT * FROM "'||:object||'9" WHERE "DocEntry" = '||:DocKey||')'; 
EXEC (:doc_1);
doc_1:= 'INSERT INTO DOC10 (SELECT * FROM "'||:object||'10" WHERE "DocEntry" = '||:DocKey||')'; 
EXEC (:doc_1);


CREATE GLOBAL TEMPORARY TABLE TempTab
(
	DocEntry int,
	LineNum int,
	_LineNum INT,
	_LineSeq INT, 
	LineType NCHAR(1), 
	TotalLevel INT,	
	SubTotalQty NUMERIC(19, 6),
	SubTotal NUMERIC(19, 6), 
	TotalFrgn NUMERIC(19, 6), 
	TotalSumSy NUMERIC(19, 6), 
	GTotal NUMERIC(19, 6), 
	GTotalFC NUMERIC(19, 6), 
	GTotalSC NUMERIC(19, 6), 
	VatSum NUMERIC(19, 6), 
	VatSumFrgn NUMERIC(19, 6), 
	VatSumSy NUMERIC(19, 6),
	LineText NCLOB		
);


INSERT INTO TempTab(SELECT "DocEntry", "VisOrder", "VisOrder", -1, "LineType", 0, 0.0, 0.0, 0.0, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, '' FROM "DOC1");
INSERT INTO TempTab(SELECT "DocEntry", -1, "AftLineNum", "LineSeq", "LineType", 0, 0.0, 0.0, 0.0, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, "LineText" FROM "DOC10");


DECLARE CURSOR curTemp FOR SELECT "LineType", "_LineNum", "_LineSeq" FROM TempTab ORDER BY "DocEntry", "_LineNum", "_LineSeq";
  
TotalLevel:= 0;
FOR cur_row as curTemp DO
	IF cur_row = 'S' THEN
		SELECT * FROM TempTab;
	END IF;
END FOR; 
  
/*Drop temp table*/
DROP TABLE DOC;	
DROP TABLE DOC1;	
DROP TABLE DOC3;	
DROP TABLE DOC5;
DROP TABLE DOC9;
DROP TABLE DOC10;	
DROP TABLE TempTab;
			
END;


CALL "SBODEMOAU"."spAppMarketingDoc" (1, 13)
 
 

Stuck at the "DECLARE CURSOR"

Hope somebody can help me.

Here's the link for the SQL Query: https://answers.sap.com/questions/71728/help-sap-b1-create-standard-document-query.html

Thanks,