Sub Query issue with Max

[#1]

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

PSOB a,
PSCOMP b,

where

 b.EFFDT =
        (SELECT MAX(D_ED.EFFDT)
        FROM PSCOMP D_ED
        WHERE b.COMPANY = D_ED.COMPANY
          AND D_ED.EFFDT <= C.EFFDT) - this is where impala showing error

Error Message

AnalysisException: Unsupported aggregate subquery with non-equality correlated predicates:

Thanks

[#2]

Hi,
I think you most to use select with the analytical function row_number() -with order by D_ED.EFFDT desc- instead of a nested query in join.
Good luck.

[#3]

Thanks.

Can you pls post a sample

[#4]

Before that, I remark that there is two errors in your query
1- This comma must be deleted!

PSCOMP b,

2- You use C like an table alias but there is no table alias C in the query!!

C.EFFDT

NB: Also try to write all the query with the lowercases to be more clear (eg. C.EFFDT ..b.effdt !!).

[#5]

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

from

psob a,
pscomp b

where

b.effdt = ( select max(d.effdt) from pscomp d
                  where b.company = d.company
                 and d.effdt <= a.effdt)

[#6]

Hi @mycloud

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.