I have a view which contains 1706 rows. I am trying to retrieve the whole data without passing any filters.
But facing the retrieval error and Memory Circuit error.
I applied selection, filters etc to optimize the code.
I am trying to display this entire data in a Grid. So, grid should would irrespective of the filters provided. I understand the issue is because of timeout/large data.
Please help me on how to optimize it to work.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="urn:com:appian:types:CTRSTL7" targetNamespace="urn:com:appian:types:CTRSTL7"> <xsd:complexType name="CTRS_TL7_V_CLOSED_FORMAL_CASE"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Table(name="TL7_V_CLOSED_FORMAL_CASE")</xsd:appinfo> <xsd:documentation><![CDATA[CDT for the Data for Closed Formal Case Reports Data]]></xsd:documentation> </xsd:annotation> <xsd:sequence> <xsd:element name="TL7_V_CLOSED_FORMAL_CASE_ID" nillable="true" type="xsd:int"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Id @Column(name="TL7_V_CLOSED_FORMAL_CASE_ID", columnDefinition="NUMBER")</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="TL7_CASE_ID" nillable="true" type="xsd:int"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="TL7_CASE_ID", nullable=false, columnDefinition="NUMBER")</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="TL7_R_DISPOSITION_ACT_ID" nillable="true" type="xsd:int"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="TL7_R_DISPOSITION_ACT_ID", columnDefinition="NUMBER")</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="CLOSURE_REASON" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="CLOSURE_REASON", columnDefinition="VARCHAR2(60)")</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="AGENCY" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="AGENCY", columnDefinition="CHAR(5)")</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="FORMAL_CASE_NUMBER" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="FORMAL_CASE_NUMBER", columnDefinition="VARCHAR2(15)")</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="COMPLAINANT_NAME" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="COMPLAINANT_NAME", columnDefinition="VARCHAR2(102)")</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="DATE_FILED" nillable="true" type="xsd:date"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="DATE_FILED", columnDefinition="DATE")</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="DAYS" nillable="true" type="xsd:int"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="DAYS", columnDefinition="NUMBER")</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="DATE_CLOSED" nillable="true" type="xsd:date"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="DATE_CLOSED", columnDefinition="DATE")</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="COMMENTS" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="COMMENTS", columnDefinition="CLOB")</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="FISCAL_YEAR" nillable="true" type="xsd:int"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="FISCAL_YEAR", columnDefinition="NUMBER")</xsd:appinfo> </xsd:annotation> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:schema>
a!localVariables( local!pagingInfo: a!pagingInfo( startIndex: 1, batchSize: 1700, sort: a!sortInfo( field: "DATE_FILED", ascending: true() ) ), local!closedFormalComplaintsData: rule!CTRS_TL7_QE_V_getClosedFormalCaseData( dispositionActId: ri!dispositionActid, pagingInfo: local!pagingInfo ).data, local!complainsAggregateData: rule!CTRS_TL7_QE_V_getClosedFormalCaseDataCount( dispositionActId: ri!dispositionActid, ).data, { a!gridField( label: union( index( local!closedFormalComplaintsData, "CLOSURE_REASON", {} ), index( local!closedFormalComplaintsData, "CLOSURE_REASON", {} ) ), labelPosition: "ABOVE", data: local!closedFormalComplaintsData, columns: { a!gridColumn( label: "#", value: fv!identifier ), a!gridColumn( label: "Discrimination Agency", value: fv!row.agency, sortField: "AGENCY" ), a!gridColumn( label: "Formal Case Number", value: a!linkField( links: a!recordLink( label: fv!row.FORMAL_CASE_NUMBER, recordType: cons!CTRS_TL7_REC_CASE, identifier: fv!row.TL7_CASE_ID ) ) ), a!gridColumn( label: "Complainant name", value: fv!row.COMPLAINANT_NAME, sortField: "COMPLAINANT_NAME" ), a!gridColumn( label: "Formal Date", value: fv!row.DATE_FILED, sortField: "DATE_FILED" ), a!gridColumn( label: "Total Days", value: fv!row.DAYS, sortField: "DAYS" ), a!gridColumn( label: "Closure Date", value: fv!row.DATE_CLOSED, sortField: "DATE_CLOSED" ), a!gridColumn( label: "Comments", value: a!richTextDisplayField( value: a!richTextIcon( icon: "commenting", caption: if(isnull(fv!row.COMMENTS),"N/A",fv!row.COMMENTS) ) ), sortField: "COMMENTS" ) }, pagingSaveInto: local!pagingInfo, initialSorts: local!pagingInfo.sort, pageSize: local!pagingInfo.batchSize ), a!gridField( label: "Average", data: local!complainsAggregateData, columns: { a!gridColumn( label: "Number of Days", sortField: "TOTAL_DAYS", value: fv!row.TOTAL_DAYS, align: "END" ), a!gridColumn( label: "Number of Records", sortField: "RECORD_COUNT", value: fv!row.RECORD_COUNT, align: "END" ), a!gridColumn( label: "Average Days", sortField: "AVERAGE_DAYS", value: fv!row.AVERAGE_DAYS, align: "END" ) } ) } )
Attached the Details.
/*if(and(isnull(ri!dispositionActId),isnull(ri!fiscalYear)),{},*/ a!queryEntity( entity: cons!CTRS_TL7_ENTITY_V_CLOSED_FORMAL_CASE, query: a!query( selection:if( rule!CTRS_UTILITY_RULE_checkisNull(ri!selection),{}, a!querySelection( columns: { a!queryColumn( field: "AGENCY" ), a!queryColumn( field: "FORMAL_CASE_NUMBER" ), a!queryColumn( field: "COMPLAINANT_NAME" ), a!queryColumn( field: "DATE_FILED" ), a!queryColumn( field: "DAYS" ), a!queryColumn( field: "DATE_CLOSED", ), a!queryColumn( field: "COMMENTS" ) } ) ), logicalexpression: a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: "TL7_R_DISPOSITION_ACT_ID", operator: "=", value: ri!dispositionActId, ignoreFiltersWithEmptyValues: true ), a!queryFilter( field: "FISCAL_YEAR", operator: "=", value: ri!fiscalYear, ignoreFiltersWithEmptyValues: true ) }, ignoreFiltersWithEmptyValues: true ), pagingInfo: a!pagingInfo( startIndex: 1, batchSize: - 1, sort: a!sortInfo( field: "TL7_V_CLOSED_FORMAL_CASE_ID", ascending: true ) ) ), fetchTotalCount: true )
Discussion posts and replies are publicly visible