EPM 12 MT - Problem with clausule ORDER BY in a SQL Statement from Application Studio

Llorenç Tomas

Hi, we have a Hyperblock pointing to a relational table thru a SQL Statement with parameters pointing to report variables in the clausule 'WHERE' working successfully. Problem is when putting another report variable in the 'ORDER BY' clausule getting an error but if we put the value manually in the SQL sentence ('DDIVISION' in this case) returns the information correctly:

Any idea to solve this issue?

Thanks in advance,

Kind Regards

John Robson

This query shows a possible solution using SQL CASE statements :

SELECT
[ID]
,[ElementType]
,[ParentID]
,[Weight]
,[English]
,[OrderPosition]
,[GLCode]
,[Country]
FROM [dEPMStaging].[dbo].[Dim_Company]
ORDER BY
CASE @Order WHEN 1 THEN [ID] END ASC,
CASE @Order WHEN 2 THEN [ID] END DESC;

The variable linked to the Order parameter should be set to 1 to sort in ascending order or 2 for descending in this example. I don't recall why I used integers as values. The example was OP. For MT you would simplify the FROM clause.