CODEWITHSUNDEEP
sqlhana
Senthil
Senthil

Reputation: 189

How to write SQL statements in SAP HANA Scalar User Defined Functions (UDF)?

I am writing a UDF (User Defined Function) in SAP HANA. I want to use this function in another SQL statement outside.

Example:

I want to write a UDF like

func_get_age (in_employee_id)

This function takes in in_employee_id as an input parameter. Inside the UDF, it performs a lookup on a table for the particular in_employee_id and returns the age. So, a SQL goes inside the UDF. Something like

SELECT AGE FROM EMPLOYEE WHERE EMPLOYEE_ID = in_employee_id

Now this Age needs to be returned back as an output which is then consumed by another SQL. Something like

SELECT func_get_age(11234) from dummy;

My question is

Is it possible to write SQL inside UDFs in SAP HANA? Again i want the UDF to return a single value rather than a Table. I am not looking for Table Functions.

Upvotes: 0

Views: 4276

Answers (2)

Eralper
Eralper

Reputation: 6612

Yes, sure You can create user-defined scalar function on HANA database as given in the sample where fullname of the employee is returned

I copy a simplified version of the HANA UDF function below

CREATE FUNCTION fnGetFullname (
 firstName VARCHAR(40),
 middleName VARCHAR(40),
 lastName VARCHAR(40)
)
returns fullname VARCHAR(120)
AS
BEGIN

fullname := CONCAT(CONCAT(CONCAT(firstName, ' '), CONCAT(middleName,' ')),lastName);

END

You just assign a value to the return value

Here how you can use the function

select fnGetFullname('Sith Lord','Darth','Vader') from dummy;

Upvotes: 1

Lars Br.
Lars Br.

Reputation: 10388

Yes, that's perfectly possible in current HANA releases.

For example: 

create or replace function multi (in mult int)
returns multi_count int
as 
begin
declare cnt integer;

    select count(*) into cnt 
    from objects;

    multi_count := :cnt * :mult;
end;

This function takes the number of all visible objects and multiplies it with the input parameter value. The result of this operation is assigned to the RETURN parameter multi_count.

select multi (3) from dummy;

Upvotes: 1

Related Questions