SQL Query, Sorting Dynamically

I have a drop down list, which a client can select a column variable of a db table to sort

But it seems like advance query won't take this query


where in @In1 is from the dropdownlist value so i tried [Name] or Name

and gives error "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."

Not only that the Sort Direction is also dynamic

ORDER BY @In1 @In2

Ultimatelly my sql query will look something like

SELECT [Company],[Type],[Function] FROM {Sheet1} ORDER BY Company ASC, CASE (DENSE_RANK() OVER (ORDER BY [Company]) % 2) WHEN 1 THEN [Type] END ASC, CASE (DENSE_RANK() OVER (ORDER BY [Company]) % 2) WHEN 0 THEN [Type] END DESC, CASE (DENSE_RANK() OVER (ORDER BY [Type]) % 2) WHEN 1 THEN [Function] END DESC, CASE (DENSE_RANK() OVER (ORDER BY [Type]) % 2) WHEN 0 THEN [Function] END ASC
Any where that says Company / Type / Asc / Desc or additional column variable will have to be dynamic from the drop down list

So I am wondering how can I dynamically choose to sort a XXXX column without hard coding?


What you're trying to do seems very similar to deploying a webpage using the RichWidgets\List_SortColumn widget to pick which column header you sort on (and by repeated selection, the ASC/DESC).  The standard query object based on your table would then accept the 'Order By' as a parameter and resort the output accordingly.

You didn't specify why you needed the Advanced SQL object vs. a standard query, but if you can't get away with a standard query then while the performance might not be great you could perhaps pipe the Advanced SQL object to a defined output structure without any sorting, then pass that output to a 'standard' query object that accepts parameter input for what you want sorted.

Then theoretically, if you didn't need to rerun the special functions in your Advanced SQL every time, you could just refresh the second standard query with your new sort directions after each UI update.

That's all I could think of.
Everything works now.

the above query starting after order by can be generated with for each widget as well as if.

Then all I need to do is select ... From ... Order by @In1 

And in the settings of In1 select Toggle a yes or no text box which allows to read in as string which in turns translate to actual order by script