Reply
New Contributor
Posts: 3
Registered: ‎04-18-2018

Sub Query issue with Max

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

Expert Contributor
Posts: 142
Registered: ‎07-17-2017

Re: Sub Query issue with Max

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.
New Contributor
Posts: 3
Registered: ‎04-18-2018

Re: Sub Query issue with Max

Thanks.

 

Can you pls post a sample

Expert Contributor
Posts: 142
Registered: ‎07-17-2017

Re: Sub Query issue with Max

[ Edited ]

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 !!).

New Contributor
Posts: 3
Registered: ‎04-18-2018

Re: Sub Query issue with Max

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)

Highlighted
Expert Contributor
Posts: 142
Registered: ‎07-17-2017

Re: Sub Query issue with Max

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.