3 Replies Latest reply: Mar 18, 2014 7:58 AM by Joe R RSS

    ORACLE-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc. On aTab Form

    Joe R

      Version 4.1.1.00.23

       

      Hello,

       

      I'm receiving the above error trying to implement a Search field as described: https://community.oracle.com/thread/3531549


      The query is:


      WITH data
           AS (SELECT TRIM(SUBSTR(txt,INSTR(txt,CHR( 10 ),1,LEVEL) + 1, INSTR(txt,CHR( 10 ),1,LEVEL + 1) - INSTR(txt,CHR( 10 ),1,LEVEL) - 1)) AS token
               FROM   (SELECT CHR( 10 ) || :P105_SEARCH || CHR( 10 ) txt FROM DUAL)
               CONNECT BY LEVEL <=   LENGTH( :P105_SEARCH ) - LENGTH(REPLACE(:P105_SEARCH,CHR( 10 ),'')) + 1)
      SELECT recommendation.gap_rec_id
            ,rec_id
            ,gap_id
            ,criticality
            ,recommendation.sort_order sort_order
            ,tasks
            ,working_team
            ,recommendation.completion_date
            ,rec_name
            ,status.status
            ,severity
            ,gap_code.code_value source
            ,top_8
            ,gc2.code_value cme_priority
            ,owner.owner
            ,gap_desc
             ,theme_name
            ,LISTAGG(ryg_project.proj_name,', ') WITHIN GROUP (ORDER BY ryg_project.proj_name) AS associated_projects
      FROM   recommendation
             LEFT JOIN gap_code
                 ON recommendation.source = gap_code.code_id AND
                    gap_code.code_category_id IN (SELECT gc3.code_category_id
                                                  FROM   gap_code_category gc3
                                                  WHERE  gc3.code_category_value = 'Gap Source')
             LEFT JOIN gap_code gc2
                 ON recommendation.cme_priority = gc2.code_id AND
                    gc2.code_category_id IN (SELECT gc4.code_category_id
                                             FROM   gap_code_category gc4
                                             WHERE  gc4.code_category_value = 'CME Priority')
             LEFT JOIN (SELECT resource_onepass_id
                              ,resource_name owner
                        FROM   resources
                        WHERE  resource_onepass_id IS NOT NULL) owner
                 ON owner.resource_onepass_id = recommendation.owner
             LEFT JOIN (SELECT code_id
                              ,code_value status
                        FROM   gap_code) status
                 ON status.code_id = recommendation.status
             LEFT JOIN theme ON recommendation.theme_id = theme.theme_id
             LEFT JOIN proj_rec ON recommendation.gap_rec_id = proj_rec.gap_rec_id
             LEFT JOIN ryg_project ON proj_rec.proj_id = ryg_project.ryg_proj_id
      WHERE  ( recommendation.gap_rec_id IN (SELECT gap_rec_id
                                             FROM   proj_rec 
                                             LEFT JOIN ryg_project ON proj_rec.proj_id = ryg_project.ryg_proj_id
                                             WHERE  :P105_PROJECT = ryg_project.ryg_proj_id) OR :P105_PROJECT = 0 ) AND
             ( recommendation.theme_id = :P105_THEME OR :P105_THEME = 0 ) AND
             ( status.status = :P105_STATUS OR :P105_STATUS = 0 ) AND
             ( cme_priority = :P105_PRIORITY OR :P105_PRIORITY = 0 ) AND
             ( top_8 = :P105_TOP8 OR :P105_TOP8 = 0 ) AND
             ( ryg_project.proj_name IN (SELECT p.proj_name
                                         FROM   ryg_project p
                                                INNER JOIN proj_rec pj ON p.ryg_proj_id = pj.proj_id
                                                INNER JOIN recommendation ON pj.gap_rec_id = recommendation.gap_rec_id
                                         WHERE  ryg_project.ryg_proj_id = :P105_PROJECT) OR :P105_PROJECT = 0 ) OR
              (tasks IN (SELECT UPPER(REPLACE(token,CHR( 13 ),'')) FROM data) OR :P105_SEARCH IS NULL) 
      GROUP BY recommendation.gap_rec_id
              ,rec_id
              ,gap_id
              ,criticality
              ,recommendation.sort_order
              ,tasks
              ,working_team
              ,recommendation.completion_date
              ,rec_name
              ,status.status
              ,severity
              ,gap_code.code_value
              ,top_8
              ,gc2.code_value
              ,owner.owner
              ,gap_desc
               ,theme_name
      

       

      When I remove the WITH clause and the condition on line 59 the page runs and displays the data. The ROWID is NOT being selected in the query, the Primary Key is. So the WITH clause cannot be used in a Tab Form with a query that contains a GROUP BY?

      When I removed the LISTAGG (line 22) and the GROUP BY I received the error: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

       

      Can anyone help with getting this to work?

       

      What information can I provide?

       

      Thanks,

      Joe