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
0 additional answers
Sort by: Newest
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
The Real data type is already in the original query written by DavidChase who, I guess, knows its specifics.
In any case, if necessary, you can read here:
[https://learn.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-ver15][1]