Dynamic Sorting - Advanced SQL when using a Union All

Dynamic Sorting - Advanced SQL when using a Union All

  

Hi Guys,

I am looking to create a union between 2 none related tables to make a new table. To do so I have used advanced query and then applied filtering however I can't get dynamic filtering to work as it errors:

"The Select item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position..."

If I hard code the ORDER BY it works correctly but not using the rich widget.


Does anyone know how to resolve this or a better way around it?


Many thanks in advance


this is my code:


Sort Variable on Advanced SQL= List_SortColumn_GetOrderBy(ReportTable.Id, "{CustomerRequests}.[Name]")



Results from hard coding the ORDER BY as CustomerRequests.[Name], so can see the structure used


Hi Richard,

Are you using "order by" at end of query, or at sub-query level? 

Can you provide the SQL?

Regards,

Hello Richard,

In Advanced Query (SQL), you can pass an Inline Parameter with part of the SQL.
There is an example on how to do this for dynamic sort in SQL here (It is the same thing for version 10): https://www.outsystems.com/help/servicestudio/9.1/index.htm#t=Using_Data%2FSQL_Parameters.htm

So, you just need to set the Expand Inline property of your attribute to YES (in your code, it is set to NO).

Cheers.

Solution

Richard Keir wrote:

this is my code:

Sort Variable on Advanced SQL= List_SortColumn_GetOrderBy(ReportTable.Id, "{CustomerRequests}.[Name]")

Hi Richard,

is CustomerRequests an Entity (given you are using the { } syntax)? otherwise just directly use the alias you are using inside your SQL statement.

Solution

Adding to Jorge's answer.

After inspecting your query, besides the fact that you really need to set Expand Inline to Yes, you have another problem.

You need to give the same name for each column on each select of the union, using AS, like:

SELECT {entity1}.[id] AS id, ...

In the reachwidget you than has to use this name tobthe sort.

Cheers

Thank you all for the advice! 

I changed to use Expand Inline for my Sort parameter and used the rich widgets to not use {} as the table wasn't an entity as suggested above.

To test it with the default, in the widget it doesn't work and complains about an incorrect syntax... however when tested after publishing it works perfectly.


Many Thanks

unfortunately I cant give credit to both Eduardo Jauch and Jorge Martins as I used both advice to resolve the issue. So have marked Jorge's answer but liked both in case it helps others in the future. Also very impressed by how quick you all were to respond and help a fellow user.


Thank you again