Using UDTF on Join clause

Question

I'm trying to use UDTF on a join and for some reason I'm receiving an error which says that the UDTF is not recognizing the 1st table on the statement:

SHOW TRANSACTIONS IN ACCOUNT; 

 

CREATE OR REPLACE TEMPORARY TABLE UTIL_DB.PUBLIC.TRANSACTIONS AS 

SELECT * from table(result_scan(last_query_id()));

 

SELECT *

FROM UTIL_DB.PUBLIC.TRANSACTIONS AS T

JOIN table(information_schema.QUERY_HISTORY_BY_SESSION(T."id")) Q

WHERE Q.EXECUTION_STATUS = 'RUNNING'

AND T."id" != CURRENT_SESSION();

ERROR:

SQL compilation error: error line 3 at position 55

invalid identifier 'T."id"'

The motivation for this query is getting the sql text of the active transaction

Can someone please advise how can I resolve it?

Thanks

user_5070879 · Answer

It seems that the issue is when trying to pass the identifier instead of "constant" into information_schema.query_history_by_session().

As an alternative information_schema.query_history() could be used:

SELECT *
FROM tab AS T
join table(information_schema.query_history()) Q
  ON T."id" = Q.session_id         -- here the join instead of correlation
WHERE Q.EXECUTION_STATUS = 'RUNNING'
  AND T."id" != CURRENT_SESSION();

Using UDTF on Join clause

1 Answers