Unable to execute queries using "TOP" in Flexible Query of HAC.

[SAPSupport]

I am writing to seek assistance regarding a challenge we are facing while attempting to execute queries with the "TOP" parameter in the Flexible Query of HAC. Specifically, we are unable to perform a simple query such as:

SELECT TOP 10 * FROM {Order}

The execution of this query triggers the following error message: "Exception message: A TOP cannot be used in the same query or sub-query as an OFFSET."

Our analysis suggests a potential issue in the conversion from Flexible Query to SQL Query. It seems that the OFFSET parameter is being enforced during conversion. For instance, when executing the query SELECT * FROM {Order} in Flexible Search and setting the Max. count to 1, the resulting SQL Query includes the following structure:

SELECT * FROM orders item_t0 WHERE (item_t0.TypePkString IN (?, ?, ?)) ORDER BY 1 OFFSET ? ROWS FETCH NEXT ? ROWS ONLY

We kindly request your guidance in comprehending the root cause of this behavior. If it is related to a specific property or configuration, we seek your assistance in rectifying it, enabling us to execute queries with the "TOP" parameter. Additionally, we are experiencing a similar issue impacting the Swagger functionality.

------------------------------------------------------------------------------------------------------------------------------------------------
Learn more about the SAP Support user and program here.

[SAPSupport · Accepted solution]

For OOTB flexible search in hac, it will add the pagination parameter automatically when converting the sql.

Instead of this, you could use it in the hac SQL query tab if you know the table name such as:

SELECT TOP 10 * FROM products

Or if you still want to use the flexible search, you could try with groovy script as below:

def models = flexibleSearchService.search("SELECT TOP 3 {pk} FROM {customer}").getResult()
models.each{
println it.getPk()
}