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

    Question

  • I keep getting this error: Conversion failed when converting the nvarchar value 'Bottle' to data type int.

    This is what I am trying to create: Select all rows from the ProductVendor Table and only the corresponding data from the Unit of Measure table.  Display columns BusinessEntityID, ProductID, StandardPrice from the Product Vendor table and the Name from the Unit of Measure table.  Write this 2 different ways – 1 using a join and the other using a WHERE clause. 

    USE AdventureWorks2008R2;
    SELECT PV.BusinessEntityID, PV.ProductID,PV.StandardPrice, UM.Name
    From Purchasing.ProductVendor PV
    Join Production.UnitMeasure UM
    On Pv.BusinessEntityID = UM.Name;
    • Edited by necea0612 Monday, September 23, 2013 1:27 AM
    Monday, September 23, 2013 1:26 AM

Answers

  • It appears that the error comes from the part "pv.BusinessEntityID = UM.name". BusinessEntityID appears to be an INT column and um.Name a VARCHAR/NVARCHAR column. When you perform an operation that involves a VARCAHR and INT values, the VARCHAR value is converted to INT. If the VARCHAR variable/column has values that cannot be converted to an INT value (such as 'bottle'), you will certainly see this error

    I do not have a copy of AdventureWorks2008R2 handy to show you a corrected query. But take a look at the point I mentioned above and you should be able to correct this by yourself.  


    My Blog

    Monday, September 23, 2013 2:02 AM
    Moderator
  • The relationship between ProductVendor and UnitMeasure is is on column UnitMeasureCode.  So you want

    SELECT PV.BusinessEntityID, PV.ProductID,PV.StandardPrice, UM.Name
    From Purchasing.ProductVendor PV
    Join Production.UnitMeasure UM
    On Pv.UnitMeasureCode = UM.UnitMeasureCode;

    I have no idea what it would mean to use a WHERE clause instead of a join.  Since you want columns from both tables in the result, doing this with a join would seem to be the obvious choice.

    Tom


    Monday, September 23, 2013 4:56 AM

All replies

  • It appears that the error comes from the part "pv.BusinessEntityID = UM.name". BusinessEntityID appears to be an INT column and um.Name a VARCHAR/NVARCHAR column. When you perform an operation that involves a VARCAHR and INT values, the VARCHAR value is converted to INT. If the VARCHAR variable/column has values that cannot be converted to an INT value (such as 'bottle'), you will certainly see this error

    I do not have a copy of AdventureWorks2008R2 handy to show you a corrected query. But take a look at the point I mentioned above and you should be able to correct this by yourself.  


    My Blog

    Monday, September 23, 2013 2:02 AM
    Moderator
  • Exactly what integer value is a "Bottle"?? Think how stupid this question is! On a scale from 1 to 10 what color is your favorite letter  of the alphabet? 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, September 23, 2013 2:49 AM
  • The relationship between ProductVendor and UnitMeasure is is on column UnitMeasureCode.  So you want

    SELECT PV.BusinessEntityID, PV.ProductID,PV.StandardPrice, UM.Name
    From Purchasing.ProductVendor PV
    Join Production.UnitMeasure UM
    On Pv.UnitMeasureCode = UM.UnitMeasureCode;

    I have no idea what it would mean to use a WHERE clause instead of a join.  Since you want columns from both tables in the result, doing this with a join would seem to be the obvious choice.

    Tom


    Monday, September 23, 2013 4:56 AM