Unfortunately, there is no way to do a direct select statement and only found this after some trial and error.
I first tried to do an "OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY" in the order by clause thinking that would be an easy bypass. However, I came to realize that the custom query will first do a COUNT first on your query, and if it returns more than the amount allowed by your client viewer, it will error out or pop up with a message saying you are returning more rows allowed.
So next I came up with doing a select in the join clause on the keyword table to bring back the documents that I am searching for. The below will limit the search results to only 10 items from the keyword value that you are searching on. You would need to replace the hsi.keyitemXXX with the keyword table number that you are trying to find documents for and the keyvaluechar with the value you'd like to search. Another item to be aware of is that in the from clause custom query box you are limited to only 200 characters. I ran into that when trying to put in multiple keyword tables.
The other option would be to create a view in the database and reference that in the custom sql statement.
Let me know if you have any questions about it. Hopefully, it will work for what you need it to.
FROM CLAUSE:
hsi.itemdata JOIN (SELECT TOP 10 itemnum,keyvaluechar FROM hsi.keyitemXXX where keyvaluechar = XXXXXXX) ki
ON hsi.itemdata.itemnum = ki.itemnum
WHERE CLAUSE:
hsi.itemdata.status <> 16
ORDER BY CLAUSE:
hsi.itemdata.itemnum
Also after poking around in the custom query table in the database, I saw they have a "addselectclause" column. Almost like they just need to enable it in the config menu for us.
