Error Converting Data type varchar to numeric

• codewatcher11

  Mr or Mrs. 500

  Points: 519

  More actions

  January 17, 2009 at 9:32 am

  #130074

  hi guys, im a newbie in this forum and im glad being a part of this programmer's den.

  anyways, im new in to sql,i know a few,but there are some things i dont know,

  and i know a great help is waiting me.

  i just wanna know guys how can i improve my sql statement,

  SELECT Orders.CustomerCode, Orders.Description, Orders.IsDelivered, Orders.OrderDate, Orders.OrderNo, Orders.RequisitionNo, Orders.Username,

  OrderedItems.DeliveredQty, CASE WHEN DeliveredQty = ItemQty THEN 'Delivered' WHEN DeliveredQty < ItemQty THEN (DeliveredQty)

  + ' items delivered.' ELSE (DeliveredQty) + ' items delivered' + (DeliveredQty - ItemQty) + ' consignment' END AS Remarks

  FROM Orders INNER JOIN

  OrderedItems ON Orders.OrderNo = OrderedItems.OrderNo

  WHERE (Orders.Username = @Username)

  i need to tell the query builder that the Fields ending with 'Qty' are numeric,

  thanks in advance

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 17, 2009 at 10:41 am

  #928704

  Actually, SQL knows they're numeric. The error comes from trying to concatenate a numeric value into a string. It's trying to convert 'delivered' into numeric, which isn't going to work.

  SELECT Orders.CustomerCode,

  Orders.Description,

  Orders.IsDelivered,

  Orders.OrderDate,

  Orders.OrderNo,

  Orders.RequisitionNo,

  Orders.Username,

  OrderedItems.DeliveredQty,

  CASE

  WHEN DeliveredQty = ItemQty THEN 'Delivered'

  WHEN DeliveredQty < ItemQty THEN CAST(DeliveredQty AS VARCHAR(10)) + ' items delivered.'

  ELSE CAST(DeliveredQty AS VARCHAR(10)) + ' items delivered' + CAST(DeliveredQty - ItemQty AS VARCHAR(10)) + ' consignment'

  END AS Remarks

  FROM Orders

  INNER JOIN OrderedItems ON Orders.OrderNo = OrderedItems.OrderNo

  WHERE (Orders.Username = @Username)

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• codewatcher11

  Mr or Mrs. 500

  Points: 519

  More actions

  January 18, 2009 at 2:10 am

  #928838

  GilaMonster (1/17/2009)

  ---

  Actually, SQL knows they're numeric. The error comes from trying to concatenate a numeric value into a string. It's trying to convert 'delivered' into numeric, which isn't going to work.

  SELECT Orders.CustomerCode,

  Orders.Description,

  Orders.IsDelivered,

  Orders.OrderDate,

  Orders.OrderNo,

  Orders.RequisitionNo,

  Orders.Username,

  OrderedItems.DeliveredQty,

  CASE

  WHEN DeliveredQty = ItemQty THEN 'Delivered'

  WHEN DeliveredQty < ItemQty THEN CAST(DeliveredQty AS VARCHAR(10)) + ' items delivered.'

  ELSE CAST(DeliveredQty AS VARCHAR(10)) + ' items delivered' + CAST(DeliveredQty - ItemQty AS VARCHAR(10)) + ' consignment'

  END AS Remarks

  FROM Orders

  INNER JOIN OrderedItems ON Orders.OrderNo = OrderedItems.OrderNo

  WHERE (Orders.Username = @Username)

  hi gilamonster!

  thanks for the help, it worked!

  one more question,

  how can i makE the fields ending with 'Qty' into two decimal places (0.00) ?

  thanks

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 18, 2009 at 2:38 am

  #928844

  What type are they at the moment?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• codewatcher11

  Mr or Mrs. 500

  Points: 519

  More actions

  January 18, 2009 at 3:17 am

  #928854

  currently its numeric(9, 3).

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 18, 2009 at 11:15 am

  #928939

  Cast them to numeric(9,2) before casting to varchar. It will cause the value to be rounded and then displayed with 2 decimals.

  eg: replace CAST(DeliveredQty AS VARCHAR(10)) with CAST(CAST(DeliveredQty AS NUMERIC(9,2)) AS VARCHAR(10))

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• codewatcher11

  Mr or Mrs. 500

  Points: 519

  More actions

  January 24, 2009 at 10:49 am

  #932243

  thanks! it worked

• shailesh-360831

  SSC Journeyman

  Points: 88

  More actions

  July 7, 2010 at 5:57 am

  #1189542

  I also incurred same error msg "Error converting data type nvarchar to numeric", solved converting non-numeric column to numeric

  Problem: TableA.EmpID = TableB.EmployeeID

  Here TableA.EmpID is varchar datatype and in other end TableB.EmployeeID was in Decimal datatype. I converted TableB.EmployeeID to varchar datatype.

  Solution: TableA.EmpID = Cast (TableB.EmployeeID as Varchar)

  Shailesh