Error when Using ORDER BY in Canned Report (SQL Server)

Technical queries
, , , ,
#1

Hello Team,

We are using Helical Insight version 6.1 with a Microsoft SQL Server database.

While creating a canned report, we are running an SQL query that includes an ORDER BY clause. But Helical Insight shows the following error:

“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.”

How can we fix this issue?

#2

Hello,

This issue happens because of how Microsoft SQL Server handles queries internally, especially when used through JDBC tools like Helical Insight.

Why this error occurs

Helical Insight internally wraps your query like this:

SELECT * FROM ( YOUR_QUERY ) AS temp

  • In SQL Server, ORDER BY is not allowed inside subqueries unless it is used with:
    • TOP
    • OFFSET
    • or FOR XML

So when your query contains only ORDER BY , it becomes invalid after wrapping.

Solution (Recommended)

Modify your query to include the TOP clause.

Example:

SELECT TOP 1000 *
FROM tablename
ORDER BY column_name;

Alternative Solutions (Advanced)

Using OFFSET-FETCH (SQL Server 2012+)

SELECT *
FROM tablename
ORDER BY column_name
OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY;

Using TOP 100 PERCENT (for derived queries)

SELECT *
FROM (
SELECT TOP 100 PERCENT *
FROM tablename
ORDER BY column_name
) AS temp;

(Note: This is sometimes optimized away by SQL Server, so use carefully.)

Key Takeaway

  • The issue is not a bug in Helical Insight, but a SQL Server restriction.
  • Always use TOP or OFFSET when using ORDER BY in canned reports.

After applying this change, your query should work without errors.