Why does my UDF mark this error when I used it in a query but not when I used it outside of it?

Question

I made a query to have a column of "Prof" and it works perfectly but when I made an UDF with same data it gives me this error Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.

This is the query I use to call the function

`SELECT `pf.ObtieneProf`(CAST(c_s_o_d_n AS INT64), CAST (c_s_o_n AS INT64), CAST(c_s_n AS INT64), CAST(cn AS INT64)) AS PROF
FROM `pf.con``

THE UDF is the next one:

`CREATE OR REPLACE FUNCTION `iaa.pf.ObtieneProf`(division INT64, operadora INT64, sucursal      INT64, contrato INT64) RETURNS STRING AS (
(
(
      SELECT NOMTER AS NOMBRE

FROM `pf.c_v` CV, 
`pf.ter` TER, 
`pf.j_c` JCO

WHERE CV.cv_ter_numid = TER.numid
  AND CV.cv_secuencia = JCO.JCO_CV_SECUENCIA 
  AND CV.cv_ter_numid = TER.NUMID
  AND CV.cv_ter_tipoid = TER.TIPOID
  AND CV.cv_ter_dvid = TER.DVID
  AND CV.cv_ccv_clave LIKE 'PROM%'

<The Problem Starts when I use the variable I gave to the function, if I give it to it in a normal query it works but not in an UDF>

  AND division = JCO.JCO_CON_SOP_OPE_DIV_NUMERO
  AND operadora = JCO.JCO_CON_SOP_OPE_NUMERO 
  AND sucursal = JCO.JCO_CON_SOP_NUMERO 
  AND contrato = JCO.JCO_CON_NUMERO 
                                                                                  
                  LIMIT 1

)
)
);`

Answer 1

Using queries ((Select ... )) inside of a UDF is possible, but there are some limitations. The use of aggegation or window function and limit is only possible if during the start of the query all parameters are know. If, like in your case, the parameters of the UDF come from a table, then limit cannot be used.

Therefore, please remove the limit and add any_value to your query:

SELECT ANY_VALUE(NOMTER) AS NOMBRE