Sql Query ::
WITH CTE (
HatchNumber,DeckNumber,orgzn,Destination,loadStatus,NumofPallets,row_num
) AS
(
SELECT
T1.HatchNumber,T1.DeckNumber,T1.orgzn,T1.Destination,T1.loadStatus,T1.NumofPallets,
ROW_NUMBER() OVER (
PARTITION BY
T1.HatchNumber,T1.DeckNumber,T1.orgzn,T1.Destination,T1.loadStatus,T1.NumofPallets
ORDER BY
T1.HatchNumber,T1.DeckNumber,T1.orgzn,T1.Destination,T1.loadStatus,T1.NumofPallets
) AS row_num
FROM
(
Select S.HatchNumber,
S.DeckNumber,
S.orgzn,
S.pallet_id,
S.Destination,
S.loadStatus,
(Select Count(Distinct(Pallet_id)) from WMS.Stock where HatchNumber=S.hatchNumber AND DeckNumber=S.DeckNumber and orgzn=S.orgzn And LoadStatus='DP') As'NumofPallets'
from WMS.Stock S
where S.HatchNumber IS NOT NULL and S.DeckNumber IS NOT NULL and S.LoadStatus='DP' and S.ship_no='200045'
) as T1
)
Syntax Error::
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near ')'.
What I have tried:
Tried with syntax editor to Remove syntax error while deleting duplicate records by using Row_Number function in Commen Type Expression (CTE) of SQL