4 Replies Latest reply on Mar 23, 2016 5:45 PM by 3020522

    ListAgg() ORA-01002: fetch out of sequence

    3020522

      I'm running a query where I don't want to include the GL column in the GROUP BY clause because I don't want to show a different row for each variation of the GL field. But I need to evaluate the field in subsequent processing, comparing it to a list of dynamically generated GL numbers. The best way I can think to do this is with a function and I'm trying ListAgg(). First off, is there a better function to use to compare the GL from each row to a list? Also, I am able to get the results below using SQL Developer, but not when I try to output it on a web page where Oracle returns a ORA-01002 error. We are using 12c in both cases. Below is the query and the output. Without ListAgg(), I have to include the GL column in the SELECT and GROUP BY and it undesirably outputs a separate row for each GL.

       

      SELECT h.h_spa_id as spa_id,h.submit_dt,h.oa_ap_date,ListAgg(gl,',') within group (order by gl) "mygl"

                  WHERE h.next_apprv= 'approverID'

                  and h.table1_id = d.table2_id

                  and h.table1_id = table3_id

                  group by h.h_spa_id,h.submit_dt,h.oa_ap_date

                  order by h.h_spa_id

       

      H_SPA_ID   SUBMIT_DT                  OA_AP_DATE                 MYGL

      1627005      1/25/2008 10:11:53 AM 1/25/2008 11:15:56 AM    52287,52287,52287,52287,52287,52287,52287,52287,52287,52287,52385,52385,52385,52385,52385,52385,52385,52385,52385,52385,52385,52385,52385,52385,52385