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

[Former Member]

Hi,

I am trying to invoke a user defined table function through a SQL snippet like below.

SELECT (SELECT * FROM PKG_ORG_ORG_HAS_CHILDREN(org_id)) has_child

from PA_ORG_OWNER

where stud_id = 'np1';

But for some reason it will not accept column name as an IN parameter

Could not execute 'SELECT (SELECT * FROM PKG_ORG_ORG_HAS_CHILDREN(org_id)) has_child from PA_ORG_OWNER os where ...' in 2 ms 451 µs .

SAP DBTech JDBC: [7] (at 47): feature not supported: field or table alias is not allowed as an input of table functions: line 1 col 48 (at pos 47)

In general our product has a LOT of small Oracle PL/SQL functions that are invoked from SQL within application code. This is a huge bottleneck while migrating to HANA. Any best practice anyone can recommend for this issue?

-Thanks

nphana

[rindia]

Hi nphana,

Instead of using single function, you can create another function and Invoke the function and can use IN parameter.
Here is the example:

CREATE FUNCTION RAJ.MY_FUNC (I_VKORG NVARCHAR (4), I_VTWEG NVARCHAR (2), 

             I_SPART NVARCHAR (2),  I_PARVW NVARCHAR (2), I_PARZA NVARCHAR (3) )
RETURNS TABLE (KUNNR NVARCHAR (10))
LANGUAGE SQLSCRIPT AS
BEGIN

  RETURN
  SELECT "ECC2HANA"."KNVP".KUNNR FROM  "ECC2HANA"."KNVP"
   WHERE "ECC2HANA"."KNVP".VKORG = :I_VKORG
     AND "ECC2HANA"."KNVP".VTWEG = :I_VTWEG
     AND "ECC2HANA"."KNVP".SPART = :I_SPART
     AND "ECC2HANA"."KNVP".PARVW = :I_PARVW
     AND "ECC2HANA"."KNVP".PARZA = :I_PARZA
;
END
;

SELECT * FROM RAJ.MY_FUNC('7500','10','00','AG','000');

Result is shown below:

  

Now I created another function so that I can use the result set of above function which is used as criteria for some other table.

I not used any input parameter for second function but can be used if required.

CREATE FUNCTION RAJ.FUNC_MY_FUNC ( )

RETURNS TABLE (NAME1 NVARCHAR (35))

LANGUAGE SQLSCRIPT AS

BEGIN

RETURN 

SELECT "ECC2HANA"."KNA1".NAME1 FROM "ECC2HANA"."KNA1"

  WHERE "ECC2HANA"."KNA1".KUNNR IN (SELECT * FROM RAJ.MY_FUNC('7500','10','00','AG','000'));

END;

Result is shown below:

   

Similarly you can do for your requirement.

Regards
Raj

[Former Member]

is that feature still not supported?

[former_member184768]

Hi nphana,

A similar conversion is mentioned in the thread here. http://scn.sap.com/thread/3424695

The UDFs are not fully mature to Oracle or SQL server level yet. I am sure it will come up soon, but currently I don't think you can pass on a table or column reference to the UDF.

Regards,

Ravi