CAST('2.' + CAST(ROW_NUMBER() OVER(ORDER BY P.PaymentID) as varchar) as real) AS ProfitCenterFlag
CAST from ROW_NUMBER
David Chase
681
Reputation points
I want to select ROW_NUMBER result as varchar and also as real value. When I use the TSQL below it throws an error of
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
I want the first column to be 'Payment 1', 'Payment 2', etc.
I want the 4th column to be a decimal number like 2.1, 2.2, etc.
Below is my query.
SELECT N'Payment ' + CAST(ROW_NUMBER() OVER(ORDER BY P.PaymentID) AS varchar) AS ProfitCenterGroup,
CASE WHEN P.AmountPaid > 0 THEN P.AmountPaid
ELSE D.PayAmount
END AS GroupTotal,
P.CheckRef AS MiscInfo,
CAST('2.' + ROW_NUMBER() OVER(ORDER BY P.PaymentID) As real) AS ProfitCenterFlag
FROM dbo.Payments AS P LEFT OUTER JOIN
dbo.PaymentsDetail AS D ON P.PaymentID = D.PaymentID
WHERE (P.RecordID = 56053)
AND (P.PayType < 10)