SQL statement with "ROW_NUMBER" function working in MSSQL Management Studio but not in Webcon designer.

0

Hi,
At the end in sql statement add "Order by ID"

Hi Fabian,

ROW_Number is not allowed, this has also been added to the info tooltip in BPS 2022:
Quote

Because of this mechanism, there are certain limitations to the query syntax, such as:
1. The query should return the data in the following format: SELECT column1, column2, ... FROM table_name
2. It is not possible to call stored procedures inside of the query
3. It is not possible to use the ROW_NUMBER() function
4. The columns must be named (e.g. SELECT column1, CAST(25.65 AS int) AS column2 FROM table_name)
5. When using the ORDER BY clause, it is necessary to use column aliases (e.g. SELECT column1 AS Id, CAST(25.65 AS int) AS column2 FROM table_name ORDER BY Id)
6. It is not possible to use clause WITH query AS (SELECT column1, column2, ...) ...

If you need it, you have to create a view and query this view.

Best regards,
Daniel

Thank you all!

Somehow "Order by ID" worked, but it might still be better to find another alternative if it is no longer officially supportet!

Kind Regards,
Fabian :)