Hello Everyone!
X++ newbie here.
I want to put a Row Number Field in my AX Table but there is no ROW_NUMBER() function in X++ (i think)
only in SQL Server, here is the code in SQL SERVER:
GO
SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1),
**ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS RowNumber**
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0
ORDER BY TerritoryName;
--------------------
Output:
FirstName LastName TerritoryName SalesYTD **RowNumber**
Lynn Tsoflias Canada 1421810.92 **1**
José Saraiva Canada 2604540.71 **2**
Garrett Vargas Canada 1453719.46 **3**
Jillian Carson Central 3189418.36 **1**
Ranjit Varkey France 3121616.32 **1**
Rachel Valdez Germany 1827066.71 **1**
Michael Blythe Northeast 3763178.17 **1**
Tete Mensa-Annan Northwest 1576562.19 **1**
David Campbell Northwest 1573012.93 **2**
Pamela Ansman-Wolfe Northwest 1352577.13 **3**
Tsvi Reiter Southeast 2315185.61 **1**
Linda Mitchell Southwest 4251368.54 **1**
Shu Ito Southwest 2458535.61 **2**
Jae Pak United Kingdom 4116871.22 **1**
**-------------------**
This is my code(which is not working):
insert_recordset mytmptable (LoadId, OrderNum, WorkId, RowNumber)
select LoadId, OrderNum, WorkId,
**ROW_NUMBER() OVER(PARTITION BY WorkId ORDER BY LoadId DESC) AS RowNumber**
from whsWorkLine join whsWorkTable
where whsWorkLine.WorkId == whsWorkTable.WorkId;
-------------------------
Can you translate the code in X++ version?
Please help me, thanks.