I have 2 tables TABLE1 & TABLE2. TABLE1 has colums COL_TB1_1, COL_TB1_2,COL_TB1_3,COL_TB1_4.
TABLE2 has columns COL_TB2_1, COL_TB2_2, COL_TB2_3, COL_TB2_4, COL_TB2_5.
Out of these columns COL_TB1_1 is the primary key of TABLE1 & COL_TB2_1,COL_TB2_2,COL_TB2_3 together forms a unique key for TABLE2,with COL_TB2_2 containing date & COL_TB2_3 containing time.
Now I want to select COL_TB1_1, COL_TB1_3, COL_TB1_4, COL_TB2_4 & COL_TB2_5, such that only the highest value of COL_TB2_2 & COL_TB2_3 is selected for each COL_TB2_1.
I need to do this using SPUFI. I have written the below query which is not working:
SELECT COL_TB1_1, COL_TB1_3, COL_TB1_4, COL_TB2_4, COL_TB2_5
FROM (SELECT COL_TB2_4, COL_TB2_5
WHERE COL_TB2_1 LIKE 'ABC%'
ORDER BY COL_TB2_2 DESC,COL_TB2_3 DESC
FETCH FIRST 1 ROW ONLY) A
WHERE A.COL_TB1_1 = COL_TB2_1
AND B.COL_TB1_2 IN ('01','02')
The above query is throwing an error code of -199 & error message:
ILLEGAL USE OF KEYWORD ORDER. TOKEN ) UNION EXCEPT WAS EXPECTED
Kindly let me know what modifications shall I make for my query to work. A completely new query is also welcome.