04-18-2018 04:39 PM
Hello All ,
I'm new to Impala - We are getting following error message when we join two tables as below.
Both tables has the data type for effdt - any suggestion how we can do it
Select A.A ,A.C , A.effdt , b.effdt
FROM PSCOMP D_ED
WHERE b.COMPANY = D_ED.COMPANY
AND D_ED.EFFDT <= C.EFFDT) - this is where impala showing error
AnalysisException: Unsupported aggregate subquery with non-equality correlated predicates:
04-19-2018 05:05 AM - edited 04-19-2018 08:58 AM
Before that, I remark that there is two errors in your query
1- This comma must be deleted!
2- You use C like an table alias but there is no table alias C in the query!!
NB: Also try to write all the query with the lowercases to be more clear (eg. C.EFFDT ..b.effdt !!).
04-19-2018 08:28 AM
Thanks - Here is the new query based on your suggestion - still the same problem - Can you pls give me a sample
select a.a,a.c, a.eeffdt,b.effdt
b.effdt = ( select max(d.effdt) from pscomp d
where b.company = d.company
and d.effdt <= a.effdt)
04-19-2018 09:17 AM
You are welcome,
In fact, I think you can do this sample query to get the results you want:
SELECT max(b.effdt), a.a, a.c, a.eeffdt FROM psob a, pscomp b WHERE b.effdt <= a.effdt; GROUP BY a.a, a.c, a.eeffdt;
Try it, else can you share with me the 2 tables schemas also the purpose of the query.