converters - 将 T-SQL Declare 语句转换为 SAP HANA

Question

我似乎无法在 SAP HANA 中使用声明语句进行查询。下面我放置了原始工作 T-SQL 版本和 SQL 转换器输出的 HANA 版本。我尝试了几个版本和组合，但每次我都会遇到错误，您也可以在下面找到这些错误。有人愿意给我一个“如何做”以便我可以复制这个吗？我还详细说明了 SAP 文档，但没有任何内容可以帮助我。我们将非常感谢您的帮助。

T-SQL 代码:

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'

翻译后的 HANA 查询:

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;

我得到的错误:

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) 

7 个语句的持续时间:13 毫秒

Answer 1

我创建了一个如下所示的表 OPCH 并插入了一行

    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');

使用 SQL 脚本创建一个过程，如下所示

    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

从 SQL 控制台调用该过程

    CALL SYSTEM.Z_CARDDUPLICATE;

它有效。结果是“发票号码已在条目 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)

如果这对您不起作用，请告诉我。