How do I correct: Conversion failed when converting the varchar value 'CashDiscount' to data type smallint

I have identified 3 columns with same issue in my view.

Conversion failed when converting the varchar value 'CashDiscount' to data type smallint

rhservanAsked:
Who is Participating?
 
ThomasianConnect With a Mentor Commented:
dbo.paymentypedim AS pt ON CAST(pt.PaymentTypeKey as varchar) = d.PaymentTypeCode
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Copy the SQL behind your view and paste it into this question.
0
 
rhservanAuthor Commented:
This is the code where the problem is:

dbo.paymentypedim AS pt ON pt.PaymentTypeKey = d.PaymentTypeCode


PaymentTypeKey(smallint) and needs to convert to text to work with d.PaymentTypeCode(varchar)

How do I convert the PaymentTypeKey to text in the view?


0
 
rhservanAuthor Commented:
SELECT
TOP (100) PERCENT t.DateKey,
COALESCE
(s.LotKey, 0) AS LotKey, CASE WHEN t .IsVoid = 1 THEN d .PaymentAmount ELSE 0 END AS VoidAmount,
CASE WHEN t .IsNoCharge = 1 THEN d .PaymentAmount ELSE 0 END AS NoCharge,
CASE WHEN pt.IsCash = 1 AND pt.IsCreditCard = 0 THEN d .PaymentAmount ELSE 0 END AS CashAmount,
CASE WHEN pt.IsCash = 0 AND pt.IsCreditCard = 1 THEN d .PaymentAmount ELSE 0 END AS CreditCardAmount,
p.TotalPaymentAmount AS FinalTicketAmount, t.TicketNumber
FROM
dbo.SourceSystemLot AS s INNER JOIN
dbo.LotDim AS l ON s.LotKey = l.LotKey INNER JOIN
dbo.CityDim AS c ON c.CityKey = l.CityKey LEFT OUTER JOIN
dbo.TicketFact AS t ON t.LocationID = s.LocationID LEFT OUTER JOIN
dbo.PaymentFact AS p ON t.TicketKey = p.TicketKey INNER JOIN
dbo.PaymentDetail AS d ON d.PaymentKey = p.PaymentKey LEFT OUTER JOIN
dbo.PaymentTypeDim AS pt ON pt.PaymentTypeKey = d.PaymentTypeCode
WHERE (s.SourceSystemKey = 2)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.