Query Entity throwing Retrieval Error while retrieving data

Question

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
)

Mike Schmitt · Answer

One thing I suggest in this case is to expose the primary key to the users and give it a name they'll remember and know to associate to each individual entry in the row - "Case ID" or "Serial Number" both seem like a good start.

legotx · Answer

If you get "error retrieving data" error, it is possible that your CDT and Data Store Entity does not line up correctly. It is possible that one field that you added in your CDT did not refresh in the DSE. 
 My suggestion to isolate this issue is to run the query independently. 
 Comment each field return and find out which field shows an error. 
 Once, you isolate that field, go and check if your CDT and Table are lined up with the columns and fields. 
 Then, go to the Data store and reimport the DSE. 
 Verify and publish.

Query Entity throwing Retrieval Error while retrieving data

Top Replies