System error in Resource Requisition page when more than 1000 requisition entries and aggregation is used. Oracle Only

Document ID : KB000031706
Last Modified Date : 14/02/2018
Show Technical Document Details

Problem:

If there are more than 1,000 records being returned when the portlet includes the attribute Book Resources a system error will occur if you try to Export to Excel or add Aggregation row.

Steps to Reproduce:

  1. Navigate to Resource Requisition page
  2. Click on configure button and select any one of the attribute Book Resources, Requirement Total Usage, Requirement Allocation, Requirement Hard Allocation to the selected columns list
  3. Create an aggregation on any attribute 
  4. Navigate to project and create more than 1000 requisitions 
  5. Navigate to Resource Requisition page

Expected Result: The page is loaded without error.

Actual Result:     The page throws a system error.

Or      

  1. Navigate to Resource Requisition page
  2. Click on configure button and select any one of the attribute Book Resources, Requirement Total Usage, Requirement Allocation, Requirement Hard Allocation to the selected columns list
  3. Navigate to project and create more than 1000 requisitions 
  4. Navigate to Resource Requisition page
  5. Export to Excel

Expected Result: an Excel file is created with header and detail rows.

Actual Result:     an Excel file is created with just the header row and no detail row.

The  app-ca.log will show similar error: 


ERROR 2015-08-12 10:04:55,753 [http-bio-80-exec-8] niku.xql2 (clarity:admin:41976041__18DF3D38-D0A5-46AC-A5AE-070945A61E15:resource.reqList) Exception in processing
java.lang.RuntimeException: com.niku.union.persistence.PersistenceException:
SQL error code: 1795
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01795: maximum number of expressions in a list is 1000
...
ORA-01795: maximum number of expressions in a list is 1000
01795. 00000 - "maximum number of expressions in a list is 1000"  

Cause:

CLRT-78892

Workaround:

Do not use aggregation, or do not export to Excel, or keep record count to under 1,000 by filtering on the Resource Requisitions portlet.

Resolution:

Currently under review.