There are 2 tables tableA and tableB. We need to fetch the code value from the latest row in the tableA which satisfies the join conditions between tableA and tableB. The query is below :
SELECT A.CODE FROM DB1.TABLEA A, DB1.TABLEB B WHERE A.KEYFLD=B.KEYFLD AND A.FIELD1='001' AND A.IND1 = 'Y' AND A.CODE <> ' ' GROUP BY 1 HAVING A.INSERT_TS = MAX(A.INSERT_TS) ;
But i am getting the error message "3504:Selected Non-Aggregate values must be a part of associate group' .
I tried using a derived table to fetch the max timestamp and the key field by having the same query. then joining with the same source table to fetch the rows.
SELECT SRC.CODE FROM DB1.TABLEA SRC, ( SELECT CODE,KEYFLD,MAX(INSERT_TS) AS MAXTS FROM DB1.TABLEA A, DB1.TABLEB B WHERE A.KEYFLD=B.KEYFLD AND A.FIELD1='001' AND A.IND1 = 'Y' AND CODE <> ' ' GROUP BY 1,2 ) DERIVED WHERE SRC.KEYFLD=DERIVED.KEYFLD AND SRC.FIELD1='001' AND SRC.IND1 = 'Y' AND SRC.CODE = DERIVED.CODE AND A.INSERT_TS=DERIVED.MAXTS;
The above query works fine. But i am not able understand whats wrong with the first query. Please help me understand the problem.