I suspect that what's happening here is that you're ending up with a SQL SELECT statement that has an OFFSET...FETCH without an ORDER BY clause. Try adding an $orderby to your query - I think that will fix things.

I should probably go into more detail here as to what's going on and why you get that error. It's not a problem with the API as such.

Bear in mind that the API is eventually going to call some of the Sage 200 business objects - and they sit on the Sage ObjectStore ORM which is responsible for ultimately creating the SQL query that will fetch the data you want.

If we consider the API call you're making (but exclude the filter, to keep things clearer) then at the business object level this is basically what's happening:

TransactionEnquiryViews tranEnquiryViews = new TransactionEnquiryViews();
tranEnquiryViews.Query.Max = 1;
tranEnquiryViews.Query.Skip = 1;
_ = tranEnquiryViews.First;

If I run that code in a Windows app then I get the same exception as reported above.

But if I do this:

Customers customers = CustomersFactory.Factory.CreateNew();
customers.Query.Max = 1;
customers.Query.Skip = 1;
_ = customers.First;

...then I don't get any problem.

However, if I go back to my original query and add a sort, then everything is OK:

TransactionEnquiryViews tranEnquiryViews = new TransactionEnquiryViews();
tranEnquiryViews.Query.Sorts.Add(new Sort(TransactionEnquiryView.FIELD_SLPostedCustomerTranDBKEY));
tranEnquiryViews.Query.Max = 1;
tranEnquiryViews.Query.Skip = 1;
_ = tranEnquiryViews.First;

We could go into a huge amount of detail here about how the Sage ObjectStore Query Analyser works and how it does paging and all that fun stuff - but we'd be here all day. The short answer is that a simple collection like Customers (which to all intents and purposes maps onto the SLCustomerAccount table) has a PrimaryKey defined. If a sort isn't explicitly defined in the Query then ObjectStore knows to use the Primary Key (which will usually also be the clustered index) as the default sort order. As a result the generated SQL is of the form SELECT...FROM...ORDER BY...OFFSET...FETCH NEXT.

The TransactionEnquiryViews, on the other hand, don't have a defined Primary Key (as they are the equivalent to a SQL view and usually join multiple tables). As a result, if you don't specify a sort order in the query then ObjectStore won't implicitly derive one - and you end up with invalid SQL where you have the OFFSET...FETCH NEXT without an ORDER BY clause.

It's actually a bit more complicated that this once you start looking at compound key paging and things like that, but for the most part a reasonable rule of thumb is to assume that for a view you'll need to define an $orderby in your query. I suppose you could argue that the API should do a better job of detecting stuff like this and add a default sort order when one is needed - but as I don't use the API I don't really care all that much.

This query was raised to Developer Services, and the following solution was given:

When using the skip parameter, you need to include an orderby clause to justify the usage of skip.
For example, if you used &$skip=5&$top=5&$orderby=id desc this would return the top 5 items, after the first 5 have been passed over, ordered by their id in a descending order.

Further information can be found here