on 06-11-2014 7:45 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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;
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.
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
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.