Skip to main content
S
sandeepkumarn0001
June 20, 2022
Question

Unexpected error executing query

  • June 20, 2022
  • 4 replies
  • 1 view

Hi,

We are using Picker field to search client name based on id, using an expression rule to get result of data subset when user enter id or client name. Minimum four char should provided by user in picker field search so that query will search the matching data and displays first 10 ids. For some ids while searching in picker field, getting an below error:

An error occurred while retrieving the data. Details: Unexpected error executing query (type:LKTableDataDT2865], query: [queryentity expression], order by: [[Sort[id asc]]], filters: [((id = TypedValue[it=3,v=9000152]) OR (clientname includes TypedValue[it=3,v=9000152]))])

Here, in LKTableData we have records around 19lakhs plus.

Regards,
Sandeep

    4 replies

    dhananjayk3480
    dhananjayk3480
    June 20, 2022

    It seems your input type passed is not as expected with client name data type in DB or even with 4 characters you have huge amount of data pulled out.

    Try out by just making it something so that it return only record in DB.

    Also share your code

    S
    sandeepkumarn0001Author
    June 21, 2022

    Thanks Dhananjay / Raviroshan,

    Please refer to the below sample code for your reference:

    a!localVariables(
    local!data: if(
    len(ri!id) < 4,
    null,
    a!queryEntity(
    entity: cons!LKTableData,
    query: a!query(
    paginginfo: a!pagingInfo(1, 10),
    selection: a!querySelection(
    columns: {
    a!queryColumn(field: "id"),
    a!queryColumn(field: "clientName")
    }
    ),
    logicalExpression: a!queryLogicalExpression(
    operator: "OR",
    filters: {
    a!queryFilter(field: "id", operator: "=", value: ri!id),
    a!queryFilter(
    field: "clientName",
    operator: "includes",
    value: ri!id
    )
    }
    )
    )
    ).data
    ),
    if(
    len(ri!id) < 4,
    a!dataSubset(data: {}, identifiers: {}),
    a!dataSubset(
    data: index(local!data, "clientName", {}),
    identifiers: index(local!data, "id", {})
    )
    )
    )

    ravir0014
    ravir0014
    June 21, 2022

    Your query is getting timed-out. You will require to optimize your query. Make sure you are querying only those columns that you need. As you have mentioned that you have 19L + records, you may need to do some workaround. Like do not query until user has provided 3 digits. 

    S
    sandeepkumarn0001Author
    June 21, 2022

    Thanks Raviroshan,

    We are querying len(ri!id) is 4 or more then only we are querying the data otherwise not. Also shared the sample code in above reply. Please refer if any optimization we can do in code please suggest.

     if(
    len(ri!id) < 4,
    null,
    a!queryEntity()

    Terms & ConditionsAccessibility statement