Relational list: parameters in order by command

Karin Förster

Hello,

I try to use parameters in an 'order by' command in a relational list (Cloud, 2022.10.01, secure mode):

select column1, column2, column3 from table order by @parameter1

A variable (returning one of the three column names) is assigned to the parameter.

Unfortunately I get the following message:

"The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name."

How do I use the parameter in this case?

Further I have to add a second parameter for asc/desc:

select column1, column2, column3 from table order by @parameter1 @parameter2

 

Thanks in advance.

Best regards

Karin

John Robson

Please try something like this:

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;

Here I am sorting on ID, with the order set by the parameter which should have a value of 1 or 2.

0712131256598360.doc