hana table function return two parameters as a table

[former_member683067]

hi,everyone ,i have a trouble with table function  like below :

FUNCTION "ZSAP_MODEL"."XXXXXXXX

::ZSF_PS001" (i_mandt char(3),i_objek char(18),i_where char(521) )
RETURNS table (matnr char(18),groes char(200))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
/*****************************
Write your function logic
*****************************/
declare row_c integer := 0;
declare o_cbstr char(200) :='';
declare matnr char(18) :='';

temp =  select atwrt as groes
from saped1.ausp
where  mandt = :i_mandt
and objek = :i_objek  and atwrt <> '';

temp1 = apply_filter(:temp,:i_where);

select count(*) into row_c from :temp1 ;

if row_c <> 0 then

DECLARE CURSOR C FOR select * from :temp1 ;

o_cbstr := '';

for R as C do
    o_cbstr := :o_cbstr || R.groes|| char(13) || '';
end for;

--select :o_cbstr as "o_cbstr" from dummy;
end if;

matnr := :i_objek  ;

select matnr into matnr from dummy;
temp = select matnr, o_cbstr from dummy;

return select * from :temp;  

END;

but  lines below  raise erros :

select matnr into matnr from dummy;
temp = select matnr, o_cbstr from dummy;

return select * from :temp;  

erros:

Could not create catalog object: return type mismatch;

TEMP[ MATNR:VARCHAR(5000) O_CBSTR:VARCHAR(5000) ] !=  [ GROES:NVARCHAR(30) ]: line 38 col 2 (at pos 859)

can someone help ?

[pfefferf]

Hello j x,

from my point of view the problem occurs, because you are using table variable "temp" two times for different result structures. The second time the type does not match.

Please change following for instance:

temp2 = select matnr, o_cbstr from dummy;
return select * from :temp2; 

Best Regards,

Florian