CAST from ROW_NUMBER

David Chase 681 Reputation points
2020-09-02T15:17:40.78+00:00

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)
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments No comments
{count} votes

Accepted answer
  1. 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: Most helpful