Issue while executing Queryentity.

Certified Senior Developer

Hi All,

We are using a queryentity to retrieve the data from DB View. While retrieving the data we are seeing below intermittent issue. Our query rule is too long, I have attached that. Any suggestions to optimize this rule.  

at function a!queryenti

a!queryEntity_22r2(
      entity: cons!My_VIEW,
      query: a!query(
        selection: if(
          rule!APN_isEmpty(ri!selectionColumns),
          {},
          a!querySelection(
            columns: a!forEach(
              items: ri!selectionColumns,
              expression: a!queryColumn(field: fv!item)
            )
          )
        ),
        logicalexpression: a!queryLogicalExpression(
          operator: "AND",
          ignorefilterswithemptyvalues: true,
          filters: {
            a!queryFilter(
              field: "abc",
              operator: "in",
              value: ri!abcs,
              applywhen: not(rule!APN_isEmpty(ri!abcs))
            ),
            a!queryFilter(
              field: "testName",
              operator: "in",
              value: ri!testName,
              applywhen: not(rule!APN_isEmpty(ri!testName))
            ),
            a!queryFilter(
              field: "bNumber",
              operator: "in",
              value: ri!bId,
              applywhen: not(rule!APN_isEmpty(ri!bId))
            ),
            a!queryFilter(
              field: "cluNumber",
              operator: "in",
              value: ri!cluNumber,
              applywhen: not(rule!APN_isEmpty(ri!cluNumber))
            ),
            if(
              rule!APN_isEmpty(ri!priorities),
              {},
              if(
                and(
                  not(isnull(ri!flowTypes)),
                  contains(
                    touniformstring(ri!flowTypes),
                    cons!FlowType[2]
                  )
                ),
                {},
                a!queryFilter(
                  field: "priority",
                  operator: "in",
                  value: { ri!priorities }
                )
              )
            ),
            a!queryFilter(
              field: "nfiType",
              operator: "in",
              value: ri!nfiTypes,
              applywhen: not(rule!APN_isEmpty(ri!nfiTypes))
            ),
            a!queryFilter(
              field: "emailSentBy",
              operator: "in",
              value: ri!emailSentBy,
              applywhen: not(rule!APN_isEmpty(ri!emailSentBy))
            ),
            a!queryFilter(
              field: "emailSentDate",
              operator: ">=",
              value: cast(
                typeof(datetime(2017, 10, 27, 23, 59, 00)),
                todatetime(ri!fromEmailSentDate & " 12:00 am")
              ),
              applywhen: not(rule!APN_isEmpty(ri!fromEmailSentDate))
            ),
            a!queryFilter(
              field: "emailSentDate",
              operator: "<=",
              value: cast(
                typeof(datetime(2017, 10, 27, 23, 59, 00)),
                todatetime(ri!toEmailSentDate & " 11:59 pm")
              ),
              applywhen: not(rule!APN_isEmpty(ri!toEmailSentDate))
            ),

          },
          logicalexpressions: {
            a!queryLogicalExpression(
              operator: "OR",
              filters: if(
                rule!APN_isEmpty(ri!submissionDescription),
                {},
                {a!forEach(
                  items: ri!submissionDescription,
                  expression: a!queryFilter(
                    field: "submissionDesc",
                    operator: "includes",
                    value: fv!item 
                  )
                ),
                a!forEach(
                  items: ri!submissionDescription,
                  expression: a!queryFilter(
                    field: "bDescription",
                    operator: "includes",
                    value: fv!item 
                  )
                )}

              ),
              ignorefilterswithemptyvalues: true()
            ),
            a!queryLogicalExpression(
              operator: "OR",
              filters: if(
                rule!APN_isEmpty(ri!workflowCreatedBy),
                {},
                a!forEach(
                  items: ri!workflowCreatedBy,
                  expression: a!queryFilter(
                    field: "workflowCreatedBy",
                    operator: "includes",
                    value: "; " & fv!item & ";"
                  )
                )
              ),
              ignorefilterswithemptyvalues: true()
            ),
            a!queryLogicalExpression(
              operator: "OR",
              ignorefilterswithemptyvalues: true(),
              filters: if(
                rule!APN_isEmpty(ri!gll),
                {},
                a!forEach(
                  items: ri!gll,
                  expression: a!queryFilter(
                    field: "gll",
                    operator: "includes",
                    value: "; " & fv!item & ";"
                  )
                )
              )
            ),
            a!queryLogicalExpression(
              operator: "OR",
              ignorefilterswithemptyvalues: true(),
              filters: if(
                rule!APN_isEmpty(ri!glsd),
                {},
                a!forEach(
                  items: ri!glsd,
                  expression: a!queryFilter(
                    field: "glsd",
                    operator: "includes",
                    value: "; " & fv!item & ";"
                  )
                )
              )
            ),
            a!queryLogicalExpression(
              operator: "OR",
              ignorefilterswithemptyvalues: true(),
              filters: if(
                rule!APN_isEmpty(ri!tradeNames),
                {},
                a!forEach(
                  items: ri!tradeNames,
                  expression: a!queryFilter(
                    field: "tradeName",
                    operator: "includes",
                    value: "; " & fv!item & ";"
                  )
                )
              ),

            ),
            a!queryLogicalExpression(
              operator: "OR",
              ignorefilterswithemptyvalues: true(),
              filters: if(
                rule!APN_isEmpty(ri!documentCodes),
                {},
                a!forEach(
                  items: ri!documentCodes,
                  expression: a!queryFilter(
                    field: "documentCode",
                    operator: "includes",
                    value: "; " & fv!item & ";"
                  )
                )
              )
            ),
            a!queryLogicalExpression(
              operator: "OR",
              ignorefilterswithemptyvalues: true(),
              filters: if(
                rule!APN_isEmpty(local!subtype),
                {},
                if(
                  and(
                    contains(
                      ri!flowTypes,
                      cons!flowType[1]
                    ),
                    contains(
                      ri!submissionTypes,
                      cons!TYPES[2]
                    )
                  ),
                  a!forEach(
                    items: ri!subTypes,
                    expression: a!queryFilter(
                      field: "subType",
                      operator: "includes",
                      value: "; " & fv!item & ";"
                    )
                  ),
                  {}
                )
              )
            ),
            a!queryLogicalExpression(
              operator: "OR",
              ignorefilterswithemptyvalues: true(),
              filters: if(
                rule!APN_isEmpty(ri!rcns),
                {},
                a!forEach(
                  items: ri!rcns,
                  expression: a!queryFilter(
                    field: "rcn",
                    operator: "includes",
                    value: "; " & fv!item & ";"
                  )
                )
              )
            ),
            a!queryLogicalExpression(
              operator: "OR",
              ignorefilterswithemptyvalues: true(),
              filters: if(
                rule!APN_isEmpty(ri!flowTypes),
                {},
                a!forEach(
                  items: ri!flowTypes,
                  expression: a!queryFilter(
                    field: "workflowType",
                    operator: "includes",
                    value: "; " & fv!item & ";"
                  )
                )
              )
            ),
            a!queryLogicalExpression(
              operator: "OR",
              ignorefilterswithemptyvalues: true(),
              filters: if(
                rule!APN_isEmpty(ri!submissionTypes),
                {},
                a!forEach(
                  items: ri!submissionTypes,
                  expression: a!queryFilter(
                    field: "submissionType",
                    operator: "includes",
                    value: "; " & fv!item & ";"
                  )
                )
              ),

            ),
            a!queryLogicalExpression(
              operator: "OR",
              ignorefilterswithemptyvalues: true(),
              filters: if(
                rule!APN_isEmpty(ri!workflowStatuses),
                {},
                a!forEach(
                  items: ri!workflowStatuses,
                  expression: a!queryFilter(
                    field: "workflowStatus",
                    operator: "includes",
                    value: "; " & fv!item & ";"
                  )
                )
              )
            )
          }
        ),
        paginginfo: if(
          rule!APN_isEmpty(ri!pagingInfo),
          a!pagingInfo(
            startIndex: 1,
            batchSize: 50,
            sort: a!sortInfo(field: "id", ascending: false)
          ),
          ri!pagingInfo
        )
      ),
      fetchtotalcount: if(
        or(
          rule!APN_isEmpty(ri!fetchTotalCount),
          ri!isData = true
        ),
        false,
        ri!fetchTotalCount
      )
    )
ty an error occured while retreiving the data. Details: unexpected error executing query 

Thanks

  Discussion posts and replies are publicly visible