Accepted answer
-
Anonymous
2020-09-02T15:31:43.177+00:00 CAST('2.' + CAST(ROW_NUMBER() OVER(ORDER BY P.PaymentID) as varchar) as real) AS ProfitCenterFlag
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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)
CAST('2.' + CAST(ROW_NUMBER() OVER(ORDER BY P.PaymentID) as varchar) as real) AS ProfitCenterFlag