Error Converting Data type varchar to numeric

Question

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 😀

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

codewatcher11

Mr or Mrs. 500

Points: 519

January 18, 2009 at 3:17 am

#928854

currently its numeric(9, 3).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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 · Answer 6

codewatcher11

Mr or Mrs. 500

Points: 519

January 24, 2009 at 10:49 am

#932243

thanks! it worked

shailesh-360831 SSC Journeyman Points: 88 More actions · Answer 7

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