cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

Hi,

I am trying to write a query something like the following in a procedure but it gives me error SAP DBTech JDBC: [7] (at 1669): feature not supported: field or table alias is not allowed as an input of table functions:

SELECT  

TempEX."M1",

TempEX."M2",

ex."E_ID",

TempEX."B_ID",

TempEX.B_Text,

ADD_SECONDS(TempEX."StartDateTime",60) AS StartDateTime,

ex."E2ID" AS ItemID,

ex."ExName" AS ItemText,

(SELECT ITEMS FROM SCHEMA."FN_GetItemCategories"(ex."E2ID",'Exhibitor','')) AS P, 

TempEX."MinTime",

TempEX."Radius",

ex."Size",

FROM SCHEMA."Escalators" ex INNER JOIN  

(

  SELECT .............................

) TempEX

ON ex."E2D"=TempEX."ItemID"

ORDER BY ex."E2ID",TempEX."StartDateTime";

Inside my function i am just trying to return a string after doing some select queries and operations. I cannot use scalar UDF because it does not support SELECT queries and if i make it table UDF then i get the above specified error. Can anyone suggest what am i missing or an alternative to achieve my purpose?

TIA.

Nikhil

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

Depending on how you generate the Item categories you may simply use a join instead.

- Lars

Former Member
0 Kudos

Hi Lars,

Inside my ItemCategories function i am trying to coalesce values of a column based on some filter conditions. I am not able to understand how do i achieve the same using one more join in the main query instead of function.

BTW i have simple question that scalar functions do not allow select queries, so i made them as table functions. Now how do i use the table functions inside my select queries?

Thanks a lot for your help

Nikhil

lbreddemann
Active Contributor
0 Kudos

Please be more concrete.

What does your input data look like? What should be the outcome?

Make an example. Don't just describe what you want to happen, but show it.

Table functions can be used, well, like tables. Not sure what you are asking or where you are stuck.

- Lars