Clarity

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 !

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

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?

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.