1

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.

  1. 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
  1. 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
  1. 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



1
  • RMSTMP_PNG.ota_activity.MODIFIED_DATE is 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.KEY from GROUP BY expression - it is strange to apply LISTAGG to single value.
    – Akina
    Sep 2, 2020 at 8:20

1 Answer 1

0

Try below. Check your SELECT and GROUP BY statements always with the ORA-00979: not a GROUP BY

Here some other usages of LISTAGG in case you need them.

  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.MODIFIED_DATE,
         RMSTMP_PNG.ota_activity.EQP_ID
ORDER BY RMSTMP_PNG.ota_activity.MODIFIED_DATE ASC

As your intended output try below. I did not know what which id you need and where is the VALUE column comes from. I know you can manage though.

 SELECT RMSTMP_PNG.OTA_ACTIVITY.EQP_ID AS ID,
         LISTAGG (RMSTMP_PNG.OTA_ACTIVITY_LOTINFO.KEY || ':' || VALUE, ',')
            WITHIN GROUP (ORDER BY RMSTMP_PNG.OTA_ACTIVITY.MODIFIED_DATE)
            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
ORDER BY RMSTMP_PNG.OTA_ACTIVITY.MODIFIED_DATE
11
  • Hi ismetguzelgun, i tried to run the code, seem like it return me the same error. Thanks
    – Yong
    Sep 2, 2020 at 8:52
  • Edited my answer. Try it @Yong Sep 2, 2020 at 8:55
  • Hi @ismetguzelgun, if without 'RMSTMP_PNG.ota_activity.EQP_ID' then it is work, but in this query i need to have 'RMSTMP_PNG.ota_activity.EQP_ID' because the data will display in gridview and at the same time i need to have order by 'RMSTMP_PNG.ota_activity.MODIFIED_DATE' to make sure the date is ACS. Thanks
    – Yong
    Sep 2, 2020 at 9:13
  • @Yong Edited. Try pls Sep 2, 2020 at 9:21
  • This should work with any given group by statement unless it is also in the select. If my last edit still does not work please share some sample data and we can see what will happen then. @Yong Sep 2, 2020 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.