Convert T-SQL Declare statement to SAP HANA

Question

I cannot seem to get the query with declare statement working in SAP HANA. Below I've put the original working T-SQL version and the HANA version output from the SQL converter. I've tried several versions and combinations, but every time I get errors which you also find below. Anybody willing to give me an "how to" so I can copy this? I also spelled out the SAP Documentations, but nothing there what could help me. Your help would be very appreciated.

The T-SQL Code:

DECLARE @NumAtCardDuplicate VARCHAR(50)
SET @NumAtCardDuplicate =
  (SELECT TOP 1 DocNum
  FROM TEST_RSCA.OPCH 
  WHERE CardCode = 'S100424'
  AND NumAtCard = '118 120 266 805')

IF @NumAtCardDuplicate IS NOT NULL
  SELECT 'Invoice number already used in entry ' + @NumAtCardDuplicate + '!'
ELSE
  SELECT '118 120 266 805'

The translated HANA query:

NumAtCardDuplicate varchar(50);

SELECT 
  (SELECT TOP 1 "DocNum" 
  FROM TEST_RSCA.OPCH 
  WHERE "CardCode" = 'S100424' 
  AND "NumAtCard" = '118 120 266 805') 
INTO NumAtCardDuplicate FROM DUMMY;

temp_var_0 integer;

SELECT :NumAtCardDuplicate INTO temp_var_0 FROM DUMMY;

IF :temp_var_0 IS NOT NULL THEN 
  SELECT 'Invoice number already used in entry ' || :NumAtCardDuplicate || '!' 
  FROM DUMMY;
ELSE 
  SELECT '118 120 266 805' 
FROM DUMMY;
END IF;

The Errors I get:

Could not execute 'NumAtCardDuplicate varchar(50)' in 1 ms 989 µs . 
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "NumAtCardDuplicate": line 1 col 1 (at pos 1) 

Could not execute 'SELECT (SELECT TOP 1 "DocNum" FROM TEST_RSCA.OPCH WHERE "CardCode" ='S100424' AND "NumAtCard" = ...' in 3 ms 578 µs . 
SAP DBTech JDBC: [337] (at 119): INTO clause not allowed for this SELECT statement: line 4 col 67 (at pos 119) 

Could not execute 'temp_var_0 integer' in 1 ms 701 µs . 
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "temp_var_0": line 1 col 1  (at pos 1) 

Could not execute 'SELECT :NumAtCardDuplicate INTO temp_var_0 FROM DUMMY' in 1 ms 976 µs . 
SAP DBTech JDBC: [467]: cannot use parameter variable: NUMATCARDDUPLICATE: line 4294967295 col 4294967295 (at pos 4294967295) 

Could not execute 'IF :temp_var_0 IS NOT NULL THEN SELECT 'Invoice number already used in entry ' || ...' in 1 ms 560 µs . 
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "IF": line 1 col 1 (at pos 1) 

Could not execute 'ELSE SELECT '118 120 266 805' FROM DUMMY' in 1 ms 338 µs . 
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "ELSE": line 1 col 1 (at pos 1) 

  SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "END": line 1 col 1 (at pos 1)

Duration of 7 statements: 13 ms

score 2 · Answer 1 · answered Jul 22 '14 at 01:27

I created a tables OPCH as shown below and inserted a row

    create column table "SYSTEM"."OPCH"( "DOCNUM" VARCHAR (50) not null,
    "CARDCODE" VARCHAR (7) null,
    "NUMATCARD" VARCHAR (15) null,
    primary key ("DOCNUM") )

    insert into "SYSTEM"."OPCH" values('1','S100424','118 120 266 805');

Created a procedure with SQL Script as shown below

    CREATE PROCEDURE SYSTEM.Z_CARDDUPLICATE LANGUAGE SQLSCRIPT
    AS
    BEGIN

       declare NumAtCardDuplicate varchar2(50);

      SELECT TOP 1 DocNum 
      INTO NumAtCardDuplicate
      FROM SYSTEM.OPCH 
      WHERE CardCode = 'S100424'
      AND NumAtCard = '118 120 266 805';

    IF :NumAtCardDuplicate IS NOT NULL THEN 
      SELECT 'Invoice number already used in entry ' || :NumAtCardDuplicate || '!' 
      FROM DUMMY;
    ELSE 
      SELECT '118 120 266 805'   
      FROM DUMMY;
    END IF;

    End

Called the procedure from SQL Console

    CALL SYSTEM.Z_CARDDUPLICATE;

It works. Result is "Invoice number already used in entry 1!"

    Statement 'CALL SYSTEM.Z_CARDDUPLICATE' 
    successfully executed in 132 ms 11 µs  (server processing time: 29 ms 588 µs)
    Fetched 1 row(s) in 0 ms 10 µs (server processing time: 0 ms 0 µs)

Let me know if this does not work for you.

The obvious difference is that you are using a procedure whereas OP may not have been. Is there a way to do this without creating a procedure? Why would ad-hoc queries with variables and cursors not be supported? — Peder Rice, Dec 01 '14 at 20:02

score 1 · Answer 2 · answered Jan 27 '17 at 08:09

To execute SQLScript on SQL Console, you are not required to create procedures any more in fact

Simply surround your SQL code between DO BEGIN ... END; clause as follows

do begin

     declare NumAtCardDuplicate varchar(50);

      SELECT TOP 1 DocNum 
      INTO NumAtCardDuplicate
      FROM KODYAZ.OPCH 
      WHERE CardCode = 'S100424'
      AND NumAtCard = '118 120 266 805';

    IF :NumAtCardDuplicate IS NOT NULL THEN 
      SELECT 'Invoice number already used in entry ' || :NumAtCardDuplicate || '!' 
      FROM DUMMY;
    ELSE 
      SELECT '118 120 266 805'   
      FROM DUMMY;
    END IF;

end;

Please note that I used the same code block that Reddy has suggested to use

Convert T-SQL Declare statement to SAP HANA

2 Answers2