Athena Query - Correlated subquery not supported

0

This query works outside of Athena (in DB2). For the most part it's working in Athena when I comment out the "having max(column1) > 3" line. But, the error that Athena returns when I RUN, is saying SYNTAX_ERROR: line 23:9: Given correlated subquery is not supported. The line corresponds with "and not exists":

select
*
from t1
where
product = 'SHIRT'
and t1.year = '2022' and t1.month = '07' and t1.day ='31'
and not exists
(
select max(column1) from a2
        where trim(a2.id) = trim(t1.id) --these are strings with trailing spaces, hence the trim here
        having max(column1) > 3
        )
limit 10

It's not like Athena hates the having clause because I wrote a statement as such and it worked: select max(column1) from a2 having max(column1) > 3

It's not the "and not exists" statement because I have used that previously and it worked.

I cannot figure out what Athena is fussing about.

preguntada hace 2 años2252 visualizaciones
2 Respuestas
0

I've run into the same thing in Hive, but I'm not familiar with Athena. Try coding the A2 table as a join statement, not in the where clause and then in the where clause do the filtering. Let me know if that works. Here's an example of how that's done.
https://stackoverflow.com/questions/37215092/working-around-unsupported-correlated-where-subqueries-in-hive

respondido hace 2 años
0

NOT EXISTS is indeed supported in presto and thus in Athena too. The error is also not very descriptive to conclude the reason. I found one issue with your query that you are using HAVING clause without GROUP BY. Try to make amendment's in your query based on your requirements.

AWS
INGENIERO DE SOPORTE
Shubh
respondido hace 2 años

No has iniciado sesión. Iniciar sesión para publicar una respuesta.

Una buena respuesta responde claramente a la pregunta, proporciona comentarios constructivos y fomenta el crecimiento profesional en la persona que hace la pregunta.

Pautas para responder preguntas