CODEWITHSUNDEEP
sqlsql-server
mustang00
mustang00

Reputation: 313

Conversion failed when converting the nvarchar value '....' to data type int in SQL Server

I'm trying to display a list of item names alongside its respective quantities from my OrderItem table based on a specific OrderID but the query isn't working and its displaying the conversion failed error..

The query I've constructed successfully displays a list of ItemIDs with quantities but I want it to display the ItemNames instead. The problem is that it's only the ItemID that can be accessed in the OrderItem table.

I did a bit of digging and I tried using INNER JOIN but I getting this error, so I'm assuming that I'm sort of on the right track?

Conversion failed when converting the nvarchar value 'CardiVas' to data type int.

Here's the query:

SELECT 
    OrderItem.ItemID, 
    SUM(OrderItem.Quantity) AS NumOfOrderItems
FROM 
    OrderItem 
INNER JOIN 
    Item ON Item.Name = OrderItem.ItemID, [Order] 
WHERE 
    [OrderItem].OrderID  = [Order].OrderID
GROUP BY  
    OrderItem.ItemID;

Additionally, I did a bit of digging on the conversion error and tried the methods using CONVERT and CASE below but it didn't work either and I still got the same error: 

CONVERT(NVARCHAR(20), OrderItem.ItemID)

and

CASE(NVARCHAR(20, OrderItem.ItemID)

Can anyone give me a solution to this or perhaps a better way of displaying the data I require?

If there is any additional code or info required please let me know and I'll add it to the question.

Upvotes: 0

Views: 3428

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270893

You don't need to change the join condition to show the names:

SELECT i.ItemName, SUM(oi.Quantity) AS NumOfOrderItems
FROM OrderItem oi INNER JOIN
     Item i
     ON i.ItemId = oi.ItemID
GROUP BY i.ItemName;

You also don't need the Order table to get the counts you want. (And Order is a lousy name for a table because it is a SQL keyword.)

Upvotes: 2

Related Questions