I want to join 3 table together and display the output as below.But i encountered the error when i tried to use group by and order by together. Is there any solution can solve this error? Below is my Oracle query. Appreciate if someone could help on this error. Thank you.
- The database data is like below: This is my 1st table (RMSTMP_PNG.ota_activity_lotinfo):
ID KEY VALUE AD9FBCCC49F06446E050280A2 Quantity 100 AD9FBCCC49F06446E050280A2 PackageLeads BN1140 AD9FBCCC49F06446E050280A2 TestProgram DL_FP5_SLT AD9FBCCC49F06446E050280A2 TestRev 3.0.0 AD9FBCCC49F06446E050280A2 IDV 9651AA43
Second Table as below (RMSTMP_PNG.ota_activity):
EQP_ID LOT_ID ACTIVITY MODIFIED BY MODIFIED DATE ID LAS43 9KA2909 LOAD_LOT jratnasa 18-AUG-20 AD9FBCCC49F06446E050280A2
Third Table (RMSTMP_PNG.ota_activity_rescreen)
ID RESCREEN TYPE AD9FBCCC49F06446E050280A2 FRESH
- The output i want is group all same ID as below and display in gridview but i am not sure is it possible to do that?
The final output should be like this:
EQP_ID MODIFIED DATE ID name LAS43 18-AUG-20 AD9FBCCC49F06446E050280A2 Quantity:100, PackageLeads:BN1140, TestProgram:DL_FP5_SLT, TestRev:3.0.0, IDV:9651AA43
- i come up with this query, but seem like it does not working.
SELECT RMSTMP_PNG.ota_activity.EQP_ID, RMSTMP_PNG.ota_activity.MODIFIED_DATE, RMSTMP_PNG.ota_activity_lotinfo.ID, LISTAGG(RMSTMP_PNG.ota_activity_lotinfo.KEY) WITHIN GROUP (ORDER BY RMSTMP_PNG.ota_activity_lotinfo.KEY) as "names" from RMSTMP_PNG.ota_activity INNER JOIN RMSTMP_PNG.ota_activity_rescreen ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_rescreen.ID INNER JOIN RMSTMP_PNG.ota_activity_lotinfo ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_lotinfo.ID WHERE RMSTMP_PNG.ota_activity.MODIFIED_DATE BETWEEN'01-Jan-2020'AND '30-Sep-2020' GROUP BY RMSTMP_PNG.ota_activity_lotinfo.ID, RMSTMP_PNG.ota_activity_lotinfo.KEY, RMSTMP_PNG.ota_activity.EQP_ID order by RMSTMP_PNG.ota_activity.MODIFIED_DATE ASC
RMSTMP_PNG.ota_activity.MODIFIED_DATEis not listed in GROUP BY expresion. Either add or wrap it in output list with some aggregate function (MAX for example). And I think that you must remove
RMSTMP_PNG.ota_activity_lotinfo.KEYfrom GROUP BY expression - it is strange to apply LISTAGG to single value.