Skip to Content
avatar image
Former Member

ORDER BY CASE for CURSOR - Cannot compile procedure!

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
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Apr 11, 2017 at 07:47 AM

    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

    Add comment
    10|10000 characters needed characters exceeded