Solved

SQL query builder lobby: how to sort

  • 21 February 2020
  • 7 replies
  • 1347 views

Userlevel 1
Badge +6

Hi,

We have created a lobby element with the SQL query builder.
However, we cannot enter an order by here.
By default, it sorts by machine number (but we have not set this ourselves) and we want to sort by zip code.

How can we solve this?

 

icon

Best answer by Jur 21 February 2020, 09:38

View original

7 replies

Userlevel 4
Badge +9

I haven’t found way to do it with the SQL builder itself. However I just export the Data Source, then modify the XML with an Order By. You can then just import the file back.

Userlevel 1
Badge +6

Hi Jur,

thnx for your reply, but when i change the xml file with the order by statement, i does not work.

am I doing something wrong?

 

Userlevel 4
Badge +9

You have to adjust it in the SQL section at the bottom.

 

You can also use (or abuse) this for writing more complex queries that are not possible within the Data Source Designer without creating an IAL. 

Userlevel 1
Badge +6

hi Jur,

thanks this was the solution.:thumbsup:

Does anyone have an updated solution to adding ORDER BY to the exported Query Builder Data Source Designer export/import? Doesn’t look like this works anymore? Thanks

 

 

In searching online, found this:

“Or, as the error message said, specify TOP like this: SELECT TOP (100) PERCENT ... FROM ... ORDER BY column or OFFSET like this: ORDER BY column DESC OFFSET 0 ROWS.” 

Adding the OFFSET info seems to have solved the issue, TOP didn’t create the error but also didn’t sort.

Found another issue with  OFFSET and TOP usage, first page displayed in Lobby element but subsequent pages are blank. Will have to experiment more. Otherwise, back to using views vs. Query Builder.

Reply