ERROR: key not found in SQL when trying to pass the result of a CTE as a function parameter

[JS_L]

Hi Community,
I try to pass the result of a CTE as a function parameter as code below

WITH t1 AS (
    SELECT array_join(collect_list(output), ',') AS x
    FROM my_catalog.my_db.get_x(:startTime, :endTime)
)
SELECT 'AM_offline' as Type, CASE WHEN offline_ratio > 1.5 THEN 'no-Go' ELSE 'Go' END as Status
FROM my_catalog.my_db.get_ratio (
    _startTime => :startTime, 
    _endTime => :endTime, 
    _x => (SELECT x FROM t1 LIMIT 1)
)

However, I encounter the key not found error. Can someone tell me, what is the reason of such error?

key not found: 863

Thank you.

[szymon_dybczak]

Hi @JS_L ,

Could you also attach definition of my_db.get_ration function? Most probably _x parameter expects certain datatype and when you're using subquery you're passing dataframe.

[JS_L]

Hi @szymon_dybczak 

Thanks for replying. I don't the issue is related to datatype, since the query works if I pass the subquery to _x parameter without CTE.

Please see as below code:

SELECT 'AM_offline' as Type, CASE WHEN offline_ratio > 1.5 THEN 'no-Go' ELSE 'Go' END as Status
FROM my_catalog.my_db.get_ratio (
    _startTime => :startTime, 
    _endTime => :endTime, 
    _x => (
        SELECT array_join(collect_list(output), ',') 
        FROM my_catalog.my_db.get_x(:startTime, :endTime)
    )
)