无法在HANA Studio中调用过程中的过程

Question

我正在尝试调用之前在new procedure中创建的一个过程。无论我怎么做，我在同一行都会出错。我原来的行是:

CALL "PAYROLLDBTEST"."ABS_GetEmployeeHistoryDetail"(:EmpID)

在此我得到错误"函数或过程的无效名称:ABS_GETEMPLOYEEHISTORYDETAILS: "

然后我试了调用"PAYROLLDBTEST/ABS_GetEmployeeHistoryDetail"(EmpID)在这个我得到了错误"sql语法错误:不正确的语法附近"(":"

所以请告诉我出了什么问题。

编辑:下面是整个过程:

CREATE PROCEDURE "PAYROLLDBTEST".GetEmploymentHistoryFunction 
(IN EmpID integer, IN StartDate timestamp, IN EndDate timestamp,OUT RETURNVALUE   NVARCHAR) 
LANGUAGE SQLSCRIPT 
AS 
BEGIN 
SELECT *, DAYS_BETWEEN("FromDate", "ToDate") + 1 AS "DaysCount" 
 FROM (SELECT "Code", "Name", "U_LineID", "U_empID", "U_Status", 
        CASE 
            WHEN ("ToDate" < :StartDate) THEN NULL 
            WHEN ("FromDate" > :EndDate) THEN NULL 
            WHEN ("FromDate" < :StartDate AND ("ToDate" BETWEEN :StartDate AND :EndDate)) THEN :StartDate 
            WHEN ("FromDate" < :StartDate AND "ToDate" > :EndDate) THEN :StartDate 
            WHEN (("FromDate" BETWEEN :StartDate AND :EndDate) AND
             ("ToDate" BETWEEN :StartDate AND :EndDate)) THEN "FromDate" 
            WHEN (("FromDate" BETWEEN :StartDate AND :EndDate) AND "ToDate" > :EndDate) THEN "FromDate" 
            WHEN ("ToDate" IS NULL AND "FromDate" < :StartDate) THEN :StartDate 
            WHEN ("ToDate" IS NULL AND ("FromDate" BETWEEN :StartDate AND :EndDate)) THEN "FromDate" 
        END AS "FromDate", 
        CASE 
            WHEN ("ToDate" < :StartDate) THEN NULL 
            WHEN ("FromDate" > :EndDate) THEN NULL 
            WHEN ("FromDate" < :StartDate AND ("ToDate" BETWEEN :StartDate AND :EndDate)) THEN "ToDate" 
            WHEN (("FromDate" BETWEEN :StartDate AND :EndDate) AND
             ("ToDate" BETWEEN :StartDate AND :EndDate)) THEN "ToDate" 
            WHEN ("FromDate" < :StartDate AND "ToDate" > :EndDate) THEN :EndDate 
            WHEN (("FromDate" BETWEEN :StartDate AND :EndDate) AND "ToDate" > :EndDate) THEN :EndDate 
            WHEN ("ToDate" IS NULL AND "FromDate" < :StartDate) THEN :EndDate 
            WHEN ("ToDate" IS NULL AND ("FromDate" BETWEEN :StartDate AND :EndDate)) THEN :EndDate 
        END AS "ToDate", "U_Position", "U_Project", "U_Sponsorship" 
    FROM (
    --select * from ABS_GetEmployeeHistoryDetails WHERE ("EmpID" IN (:EmpID))
  --select * from "PAYROLLDBTEST"."ABS_GetEmployeeHistoryDetails" WHERE ("EmpID" IN (:EmpID))
  CALL "PAYROLLDBTEST"."ABS_GetEmployeeHistoryDetails"(:EmpID,:RETURNVALUE);
 ) InnerQuery
 ) OuterQuery
 WHERE ("FromDate" between :StartDate and :EndDate OR "ToDate" between :StartDate and    :EndDate);

END;

谢谢

Answer 1

首先，确保您的过程确实位于您试图调用的模式中(在您的示例中PAYROLLDBTEST)。

您可以通过查看HANA Studio中的Catalog来检查—打开Catalog，然后打开模式PAYROLLDBTEST，然后打开文件夹Procedures。您的程序应该位于此文件夹中。如果没有，请尝试刷新此文件夹。如果这个过程仍然不在这里，那么它肯定存储在另一个模式中。

第二，确保使用正确数量的形参调用过程。

您提到的过程似乎只有一个参数，EmpID，这似乎是一个输入参数。您的过程的名称是GetEmployeeHistoryDetail，因此我假设您至少有一个输出参数作为第二个参数(以获取员工历史记录的详细信息)。如果是这种情况，您应该像这样调用过程(假设您在另一个过程中，并且希望在标量或表变量中使用内部过程的输出):

declare someOutputVariable bigint;
CALL "PAYROLLDBTEST"."ABS_GetEmployeeHistoryDetail" (:EmpID, :someOutputVariable);

或者如果out参数是一个表变量。someTableVariable也可以直接作为外部过程的输出参数:

PROCEDURE "SOMESCHEMA"."yourProcedure" (
      in someInputParameter1 NVARCHAR(255),
      in someInputParameter2 BIGINT,
      out someOutputParameter1 BIGINT,
      out yourSubProcedureOutputParameter "SOMESCHEMA"."some_tabletype") 
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER AS
BEGIN
    // ... (other code logic)
    CALL "PAYROLLDBTEST"."ABS_GetEmployeeHistoryDetail" (:EmpID, :yourSubProcedureOutputParameter);
END;

Answer 2

程序应该从_SYS_BIC模式调用，请尝试以下调用:

call "_SYS_BIC"."PAYROLLDBTEST/ABS_GetEmployeeHistoryDetail"(:EmpID)

Answer 3

不能在SELECT语句的FROM部分中执行CALL。

你的程序应该像这样工作:

CALL "PAYROLLDBTEST"."ABS_GetEmployeeHistoryDetails"(:EmpID,:TEMP);
SELECT fields
FROM (
  SELECT fields, case1, case2
  FROM ( SELECT * FROM :TEMP ) InnerQuery
) OuterQuery
WHERE (conditions);