Snowflake UDF sub query erorr

Question

I have rewritten the below (orginal query) in many diferent ways but still cant seem to get it to work, same error occurs.

Snowflake CTE calling a function where a column value from the CTE is a parameter in the the function, function has it where clause (correlated sub query). Is there a specific manner how to rewrite this?

THe line WHERE op.object_id = obj_id causes the issue, when i hardcode obj_id it works.

CTE1/Table1

    With t AS (....),
            
      q as (
         SELECT 
               t.name AS my_name,
               t.object_id AS obj_id,
               MyDb.Schema.ObjectValue(t.object_id, 'MY_DATE', '2023-12-30') AS end_date
                
          FROM t
     ) 

SELECT * FROM q

FUNCTION

CREATE OR REPLACE FUNCTION MyDb.Schema.ObjectValue(obj_id INT, code varchar(255), end_date date)
RETURNS varchar(500)
AS
$$
   SELECT TOP 1 prop_val
   FROM table1 op
       JOIN table2 dd ON op.id = dd.id
       LEFT JOIN table3 tt ON tt.tran_id = op.tran_id
   WHERE op.object_id = obj_id
       AND dd.code = code
       AND tt.eff_date <= end_date 
   ORDER BY tt.eff_date DESC, tt.tran_id DESC

$$

Throws an error - unsupported subquery type cannot be evaluated. Any help greatly appreciated.

Many Thanks