cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle Function vs. HANA Table Function

Former Member
0 Kudos

Hi,

When migrate Oracle to Hana,we create some table functions to replace oracle's.But how can I get the same result in hana like in oracle.

--Oracle syntax,GetUserNames() is the function name.

SELECT

    MailID

    ,AuthorID

    ,ReceiverID

    ,GetUserNames(ReceiverID) Receiver

FROM

    AllMails

WHERE FetchTime is null

--HANA,create the same table function GetUserNames(),but it's can be only placed in from clause,How can I the get the same result as within an single SQL except call the function in another procedure.

Thanks.

Kelvin

Accepted Solutions (0)

Answers (2)

Answers (2)

lbreddemann
Active Contributor
0 Kudos

Table functions in SAP HANA can only be used in the FROM clause.

That's correct and exactly the same as in Oracle.

And it is what makes sense in the context of an SELECT statement, because table function deliver, well tables (or data structures that should be handled like tables).

What you are talking about are scalar user defined functions just as Wenjun mentioned.

So how can you now solve this problem?

Either the lookup itself is rather simple and can be done by the means of joins it could be an option to encapsulate the lookup into a SQL view.

In case it is more complex than this, then the next best option would be to use a table function altogether to generate the output.

- Lars

Former Member
0 Kudos

Hi Kelvin,

Why not use scalar UDF? CREATE FUNCTION - SAP HANA SQL and System Views Reference - SAP Library

Best regards,

Wenjun

Former Member
0 Kudos

Wenjun,

Thanks for your reply.

Reason is scalar function cannot support SQL statement,or there is any alternative way to override it.

Kelvin