Skip to Content
0

ORDER BY CASE for CURSOR - Cannot compile procedure!

Apr 11, 2017 at 06:45 AM

37

avatar image
HDB version info:
  version:             2.00.000.00.1479874437
  branch:              fa/hana2sp00


CREATE PROCEDURE PROC1 AS
	TempID NUMBER;
	CURSOR C1 FOR SELECT 1 AS ID 
					FROM Dummy 
					ORDER BY CASE 
							WHEN CAST (CURRENT_DATE AS VARCHAR(30)) LIKE '2012%' THEN 1
							WHEN CAST (CURRENT_DATE AS VARCHAR(30)) LIKE '2013%' THEN 2
							ELSE 3
							END;
BEGIN
	FOR vRec AS C1 DO
		TempID = vRec.ID;
		--- some other processing
	END FOR;
END;


Compilation fails with

Could not execute 'CREATE PROCEDURE PROC1 AS TempID NUMBER; CURSOR C1 FOR SELECT 1 AS ID FROM Dummy ORDER BY CASE WHEN ...'
SAP DBTech JDBC: [257]: sql syntax error: line 9 col 8 (at pos 274)
Could not execute 'BEGIN FOR vRec AS C1 DO TempID = vRec.ID'

If I get rid of CASE statement, then the procedure compiles.

Is this syntax not supported? I could not find any limitations in SQLScript documentation.

Thanks & Regards,
Satish
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Florian Pfeffer
Apr 11, 2017 at 07:47 AM
0

The statement looks correct from a technical point of view (w/o considering the business case :-)).

It only leads to an error if you add the cursor behind the signature, before the procedure body.

Declaring it in the body using DECLARE works.

...
BEGIN
  DECLARE CURSOR C1 FOR SELECT 1 AS ID ... ORDER ...
  ...
END;

Regards,
Florian

Share
10 |10000 characters needed characters left characters exceeded