Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Sub Query issue with Max

Sub Query issue with Max

New Contributor

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

5 REPLIES 5

Re: Sub Query issue with Max

Expert Contributor
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.

Re: Sub Query issue with Max

New Contributor

Thanks.

 

Can you pls post a sample

Re: Sub Query issue with Max

Expert Contributor

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

Re: Sub Query issue with Max

New Contributor

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

Re: Sub Query issue with Max

Expert Contributor

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.

Don't have an account?
Coming from Hortonworks? Activate your account here