on 09-04-2017 9:46 AM
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,
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.