Clarity PPM1

Expand all | Collapse all

Data extraction output is failing with group by error since 15.4 upgrade/ora 12c

Jump to Best Answer
  • 1.  Data extraction output is failing with group by error since 15.4 upgrade/ora 12c

    Posted 04-12-2018 04:04 PM

    Hello Expertians,

    Since we have upgraded to 15.4 (and Oracle 12c), we are seeing an weird behavior with CA Data extraction tool's output. We have been using it in 14.3 (with Oracle 11g) without issues and I am sure this is not a PWP/package error but more of an implementation error with the underlying SQL, but looking for some suggestions.

     

    Long story short, one of the data extractions are failing since the upgrade with an error : [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00979: not a GROUP BY expression.

     

    The SQL has correct group by expression: works still fine with 14.3/Ora 11g. Does anyone have the same error with data extracts since the migration to 15.4?

    We are using SaaS.

     

    Thanks in advance for your help/input!

     

     

    DSomick : here you go !



  • 2.  Re: Data extraction output is failing with group by error since 15.4 upgrade/ora 12c

    Posted 04-12-2018 06:27 PM

    I have never used CA Data extraction tool.  Are you able to get the offending SQL?

     

    If so I would pull it and run it directly in SqlPlus or SqlDeveloper to get more information on where in the SQL the error is being generated.

     

    To try a quick fix, you could try added NO_QUERY_TRANSFORMATION hint to get Optimizer to leave your query alone -- assuming you can update the SQL in the CA Data extraction tool.

     

    V/r,

    Gene



  • 3.  Re: Data extraction output is failing with group by error since 15.4 upgrade/ora 12c

    Posted 04-18-2018 03:43 AM

    We had a  custom JasperSoft report fail when we migrated to V15.3 (oracle v12) from V15.1 (oracle v11).  There was an incorrect syntax in the sql that worked in oracle v11, but failed in oracle v12.  It was "sr1. sr1.COP_RISK_STATUS_MAP_NLS" when referencing the attribute, note the extract "sr1. ".  Unclear why it worked in V11, but it certainly failed in V12.

     

    Can you share the SQL, as suggested by Eugene?



  • 4.  Re: Data extraction output is failing with group by error since 15.4 upgrade/ora 12c
    Best Answer

    Posted 04-19-2018 02:49 PM

    Thanks both of you, the issue was found to be a bug within Oracle 11g, which is now fixed in 12c. in 12c, the Group by clause must explicitly match the source select clause, when there are multiple case statements and loops/if clauses in the select query. 11g.

    We were able to fix the issue by rewriting the SQL with proper group by clause references the actual fields being used.