'Exception getting dataset from cache' Error After Adding a Field in Ad Hoc View based on MS SQL Datasource

Issue Description

While creating Ad Hoc View from domain based on MS SQL datasource, after adding a field in AdHoc Crosstab it shows error message:

java.lang.RuntimeException: exception getting dataset from cache

and jasperserver.log file contains error

Caused by: java.sql.SQLException: [TIBCO][SQLServer JDBC Driver][SQLServer]
The ORDER BY clause is invalid in views, inline functions, derived tables, 
subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

What is the reason of the error and how to fix it?

Resolution

As it turned out, customer used a derived table in the underlying domain with query which contained ORDER BY clause.

As stated in TIBCO JasperReports^® Server User Guide, such SQL is not valid for MS SQL Server:

https://community.jaspersoft.com/documentation/tibco-jasperreports-server-user-guide/v640/representing-derived-tables-xml

---

The preceding example is not valid for SQL Server.  SQL queries for a derived table are resolved using the JDBC driver for the data source and the derived table becomes a subquery in the generated SQL.

SQL Server requires a TOP or FOR XML clause in any subquery that uses ORDER BY.

---

In order to resolve the issue, the derived table query should be modified by adding TOP or FOR XML clause

Generally, it's not a good idea to specify ORDER BY clause in view/subquery etc:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8a239560-4272-4cca-ae31-2471edc79d2d/the-order-by-clause-is-invalid-in-views-inline-functions-derived-tables-and-subqueries-unless?forum=transactsql

https://blog.sqlauthority.com/2010/08/23/sql-server-order-by-does-not-work-limitation-of-the-views-part-1/

Solution tested with TIBCO JasperReports^® Server v.6.4.0

Ref. Case 01554414