Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error Converting Data type varchar to numeric Expand / Collapse
Author
Message
Posted Saturday, January 17, 2009 9:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 9, 2009 11:02 PM
Points: 7, Visits: 36
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 :D
Post #638710
Posted Saturday, January 17, 2009 10:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 39,866, Visits: 36,206
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 2008, MVP
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

Post #638724
Posted Sunday, January 18, 2009 2:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 9, 2009 11:02 PM
Points: 7, Visits: 36
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 :)
Post #638882
Posted Sunday, January 18, 2009 2:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 39,866, Visits: 36,206
What type are they at the moment?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #638888
Posted Sunday, January 18, 2009 3:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 9, 2009 11:02 PM
Points: 7, Visits: 36
currently its numeric(9, 3).
Post #638899
Posted Sunday, January 18, 2009 11:15 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 39,866, Visits: 36,206
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 2008, MVP
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

Post #638990
Posted Saturday, January 24, 2009 10:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 9, 2009 11:02 PM
Points: 7, Visits: 36
thanks! it worked
Post #643004
Posted Wednesday, July 7, 2010 5:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 4, 2013 11:25 PM
Points: 4, Visits: 104
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
Post #948459
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse