I am trying to create a UDF that returns a table. My query works perfectly when executed. However, the same query does not when in a UDF and I get: -
“Tabular UDFs must not be correlated”
error when running the CREATE FUNCTION script.
#############################################
CREATE OR REPLACE FUNCTION CHANGED_CASHFLOWS (DATE_FROM DATE)
RETURNS TABLE (TRANSACTION_CANCELLATION_FLAG VARCHAR
, LAST_CHANGE_TS TIMESTAMP
, TransactionDate DATE
)
RETURN
SELECT t.TRANSACTION_CANCELLATION_FLAG
, t.LAST_CHANGE_TS
, c.TransactionDate
FROM CASHFLOWS c
INNER JOIN DM_TRANSACTIONS t
ON c.TransactionID = t.BUS_TRANS_NO
WHERE t.SECURITY_TYPE IN (‘AI’,‘LF’)
AND LAST_CHANGE_TS >= DATE_FROM
#############################################
Cashflows is a VDS, whose source is DM_TRANSACTIONS, so probably that is why I am getting this error. DM_TRANSACTIONS is a table in my source database.
Is there a way around this?