CODEWITHSUNDEEP
hana
Prathamesh H
Prathamesh H

Reputation: 150

How do I pass multiple entries through an input parameter mapped from a Table Function in SAP HANA

How do I pass multiple entries through an input parameter mapped from a Table Function in SAP HANA ? I've written a Table Function with an Input Parameter say IN_FORMAT_CD. I've mapped this parameter to the one created in my calculation view. I'm able to retrieve the data when I'm passing only one value say 100. But it gives no result when I'm passing more than one value. Is there any workaround for the same ?

My table function :

FUNCTION "HADMIN"."RA.Test.Prathamesh::PH_DEMO" (IN IN_FORMAT_CD NVARCHAR(500)) 
    RETURNS TABLE (NAME NVARCHAR(10), ID NVARCHAR(10), FORMAT_CD NVARCHAR(3))
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER AS
BEGIN
RETURN

SELECT NAME,ID,FORMAT_CD
FROM
HADMIN.PH_DEMO
WHERE FORMAT_CD IN (select :IN_FORMAT_CD as FORMAT_CD from dummy);

END;

Upvotes: 0

Views: 10884

Answers (2)

Lars Br.
Lars Br.

Reputation: 10388

What you are looking for is the APPLY_FILTER function of SAP HANA SQLScript.

The following example shows how your scenario could be coded:

create function get_vals (IN id_list varchar(400))
returns table (id bigint, val varchar(40)) 
as
begin
declare myfilter varchar(450) := ' ID in (' || :id_list || ')';

 _tmp  = select id, val from some_vals;
 _tmp2 = APPLY_FILTER (:_tmp, :myfilter);

 return :_tmp2;
end;

select * 
from 
get_vals ('1, 4, 23, 4, 23, 3');

This approach will push down the unique list of IDs to be used as a filter when reading the table data. However, this is still dynamic SQL so you lose benefits like plan sharing and risk SQL injection attacks. Read more on this e.g. here.

If possible, you want to handle selection lists in your application code. This, in turn, would also give you the option to decide whether using IN-lists or inner joins against temporary tables is the best approach for your situation.

In case you want to go with the selection list as a string, you should at least make sure, that common SQL injection attacks are not used and that the "in-list" really only contains possible ID values and commas.

Upvotes: 1

Eralper
Eralper

Reputation: 6612

it is not possible to produce(!) many items from a single sql variable unless you split them

In your SQL subselect query will return only rows that FORMAT_CD column values are exactly same with IN_FORMAT_CD parameter.

If this parameter represents more than one value, then this parameter is a concatenated string representation of each sub items. So we can split them back. Splitting will produce a table on the fly which can be used for selection.

Please create the user-defined HANA Split function fnsplit that source codes can be found at referenced document

Then you can alter your function as follows assuming that each value is seperated with "," from others

ALTER FUNCTION "HADMIN"."RA.Test.Prathamesh::PH_DEMO" (IN IN_FORMAT_CD NVARCHAR(500)) 
    RETURNS TABLE (NAME NVARCHAR(10), ID NVARCHAR(10), FORMAT_CD NVARCHAR(3))
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER AS
BEGIN
RETURN

SELECT NAME,ID,FORMAT_CD
FROM
HADMIN.PH_DEMO
WHERE FORMAT_CD IN (
    select str from fnsplit(:IN_FORMAT_CD,',')
);

END;

Upvotes: 0

Related Questions