How can we achieve execution of a function for each row in the result in HANA?

[pressfit]

Hello,

We are migrating our MS SQL queries to HANA for B1 and we are stuck at one critical issue. We extensively used inline table-valued functions in MS SQL so that we can reuse our code efficiently. E.g.

CREATE FUNCTION [dbo].[UDF_AddressFormat] (@Street VARCHAR(50),  @Block VARCHAR(50))
RETURNS TABLE AS RETURN (SELECT CONCAT(@Street, ", ",  @Block) AS [Address])

The way we use it in various procedures and other other functions is:

SELECT T0.CardCode, T0.CardName, (SELECT Address FROM [dbo].[UDF_AddressFormat](T1.Street,T1.Block)) AS 'BillToAddressString'

FROM OCRD T0 INNER JOIN CRD1 T1 ON T0.CardCode = T1.CardCode AND T0.BillToDef = T1.Address AND T1.AdresType = 'B'

This works great in MS SQL. But we are not able to replicate this functionality in HANA.

We tried creating a table function in HANA but when we execute the final query as mentioned above in HANA, we get the error:

feature not supported: field or table alias is not allowed as an input of table functions

I even looked for OUTER APPLY operators to achieve this but HANA seemingly does not support that either.

Can anyone please suggest the way forward or an alternative functionality? Are we going wrong somewhere?

Note: We have much more complicated operations in the functions than this. The simple table-valued function is for demonstration purposes and keeping it easier to understand.

[pressfit]

There is a correction in the function:

(SELECT CONCAT(@Street,' - ', @Block) AS [Address])

[pressfit]

The HANA table function I have created is:

CREATE FUNCTION xyz.AddressFormat (in Street VARCHAR(50), in Block VARCHAR(50))
RETURNS TABLE (Address VARCHAR(2000))
LANGUAGE SQLSCRIPT
AS BEGIN
RETURN 
SELECT :Street || ' - ' || :Block AS Address
FROM xyz.OADM; 
END

xyz is the schema.

I am using this function as follows:

SELECT
T0."CardCode", T0."CardName", 
(SELECT Address FROM xyz.AddressFormat(T1."Street",T1."Block")) AS "BillToAddressString"

FROM
xyz.OCRD T0 INNER JOIN xyz.CRD1 T1 ON T0."CardCode" = T1."CardCode" AND T0."BillToDef" = T1."Address" AND T1."AdresType" = 'B'

On using this, we are getting the following error:
feature not supported: field or table alias is not allowed as an input of table functions

I did not consider using scalar functions because I read that scalar UDFs were very bad for performance in SQL. That's why we stuck to using inline TVFs.

[ManishPant]

Hi,

Will this work?

CREATE FUNCTION "TESTFUN" (IN Street NVARCHAR(100), IN Block NVARCHAR(100)) 
RETURNS Result nvarchar(200) LANGUAGE SQLSCRIPT AS 

BEGIN 
				SELECT  (:Street  || '-' || :Block) AS "Col"
				INTO Result
				FROM DUMMY;
END;

SELECT T0."CardCode", T0."CardName", 
TESTFUN(T1."Street",T1."Block") AS "BillToAddressString"
FROM OCRD T0 
INNER JOIN CRD1 T1 ON T0."CardCode" = T1."CardCode" AND T0."BillToDef" = T1."Address" AND T1."AdresType" = 'B'

 Thanks.

Manish