cancel
Showing results for 
Search instead for 
Did you mean: 

Not able to use procedure in view

Former Member
0 Kudos

Hi,

I have procedure named GetEmpCodeFromEmployeeID . And I am trying to use it in a select statement in a view. However its giving me error that

SAP DBTech JDBC: [328] (at 164): invalid name of function or procedure: cannot use procedure or table function in select projection column: GETEMPCODEFROMEMPLOYEEID: line 3 col 5 (at pos 164)

View that I have created is

CREATE VIEW "@HourTransactionView" AS SELECT "@TRANSACTION_HOURS"."DocEntry", "@TRANSACTION_HOURS"."DocNum",

    "@TRANSACTION_HOURS"."U_EmpCode" AS "EmpID",

    GetEmpCodeFromEmployeeID("@TRANSACTION_HOURS"."U_EmpCode") AS "EmpCode",

    GetEmployeeFullName("@TRANSACTION_HOURS"."U_EmpCode") AS "FullName", "U_TransactionType" AS "TranCode",

    GetHourTableName("U_TransactionType") AS "TableName", GetHourTransactionName("U_TransactionType") AS "TranName",

    "U_FromDate", "U_ToDate", DAYS_BETWEEN("U_FromDate", "U_ToDate") + 1 AS "NoOfDays", "U_FromTime", "U_ToTime",

    IFNULL("U_WorkHours", 0) AS "NoOfHours", IFNULL("U_OvertimeHours", 0) AS "OvertimeHours",

    IFNULL("U_PaidDays", 0) AS "PaidDays", IFNULL("U_UnpaidDays", 0) AS "UnpaidDays",

    IFNULL("U_WeekendsCount", 0) AS "Weekends", IFNULL("U_HolidaysCount", 0) AS "Holidays", "U_Remarks", "U_BatchNo",

    "U_ProjectCode" AS "ProjectCode"

FROM "@TRANSACTION_HOURS"

    INNER JOIN OHEM ON OHEM."empID" = "@TRANSACTION_HOURS"."U_EmpCode";

Message was edited by: Tom Flanagan

Accepted Solutions (0)

Answers (1)

Answers (1)

sagarjoshi
Advisor
Advisor
0 Kudos

May be you should explain how your procedure is created. Are you creating this with a RESULT View?

I believe what you need is a scalar function to achieve what you are trying to do here.

Table Function/Procedure is not supported in the Column List projections AFAIK.

Please check section 5.2 in http://help.sap.com/hana/SAP_HANA_SQL_Script_Reference_en.pdf

Former Member
0 Kudos

hi

this is my function which i am trying to call in my view function. I am using table function here.

Is it correct?

CREATE FUNCTION "PAYROLLDBTEST".GetEmpCodeFromEmployeeID

(

  -- Add the parameters for the function here

  IN EmployeeID integer

)

RETURNS table("U_EmpCode" NVARCHAR(20))

LANGUAGE SQLSCRIPT  

SQL SECURITY INVOKER

AS

BEGIN

  -- Declare the return variable here

RETURN SELECT "U_EmpCode" FROM OHEM WHERE "empID" = :EmployeeID;

END;

sagarjoshi
Advisor
Advisor
0 Kudos

As I mentioned earlier it is not possible to use Table User Defined functions in column projections. You should achieve this using different mechanisms (e.g. using table UDF in JOINs and project variable from joined table UDF)

Former Member
0 Kudos

i had created scalar function but it gave error that scalar UDF feature is not supported in HANA.

so i used table function. I am beginner to HANA DB so can you give a brief introduction on these UDF.

I have successfully created the functions but not able to call them.

Please tell me how to call procedure from another procedure?

For example i am calling here another procedure GetTakenUnpaidDays,but getting error that invalid procedure name. But it exist in my Schema.

TakenUnpaidLeaves := :TakenUnpaidLeaves + GetTakenUnpaidDays(:EmpID, :StartDate, :EndDate, :UnpaidLeave);

Thank you.

Former Member
0 Kudos

Hi Sagar,

I am facing a similar issue. I have created a simple table defined function called Split which just splits a string based on a delimeter and returns output as table. When i try to use this function in my procedure it gives an error -  "feature not supported: field or table alias is not allowed as an input of table functions:" Can you please explain your answer i.e. to use it with table JOINS... with an example.

Thanks,

Nikhil