Ganesh Ganesh - 17 days ago 6
SQL Question

How to use aggregate function in comparison

Im trying to run this query in DB2:

SELECT A.EMAIL_ADDR_ID, A.SENT_TS, B.SENT_TS FROM ecom.EMAIL_HIST A
INNER JOIN ecom.EMAIL_RTM_HIST B
ON A.EMAIL_ADDR_ID = B.EMAIL_ADDR_ID
WHERE max(b.SENT_TS) > max(a.SENT_TS)
GROUP BY A.EMAIL_ADDR_ID;


On trying to run the above query, I get this below error message.

SQL Error [42903]: Invalid use of an aggregate function or OLAP function..SQLCODE=-120, SQLSTATE=42903, DRIVER=3.64.114


Any pointers on what mistake I have done here?

Thank you!

Answer

solution 1

SELECT  A.EMAIL_ADDR_ID, max(A.SENT_TS) maxsendTS_A, max(B.SENT_TS) maxsendTS_B
FROM ecom.EMAIL_HIST A INNER JOIN ecom.EMAIL_RTM_HIST B
ON A.EMAIL_ADDR_ID = B.EMAIL_ADDR_ID
GROUP BY A.EMAIL_ADDR_ID
having max(b.SENT_TS) > max(a.SENT_TS)
Comments