[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

error converting data type varchar to real

I have this code:

dbo.Invoice.InvoiceDate, COUNT(*) AS [Order QTY],

It works well in my View, however I want to build an expression from it like this:

dbo.Products.CartonCount * 'dbo.Inventory.BoxId:OrderQty' AS TotalPieces,

When I try to run it however I get this error:

error converting data type varchar to real

Can anyone help me understand why?

Thanks
0
robynum
Asked:
robynum
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
where does this come from
dbo.Inventory.BoxId:OrderQty

in this expression, you had single quotes around it, which makes that it is interpreted as string:
dbo.Products.CartonCount * 'dbo.Inventory.BoxId:OrderQty' AS TotalPieces,
0
 
robynumAuthor Commented:
What I am trying to do is multiply the first field: dbo.Products.CartonCount by the second, but it is actually a COUNT of dbo.Inventory.BoxId with the ALIAS of OrderQTY.

Does this help?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I guess you want to do something like this:

select p.CartonCount * i.OrderQty
from dbo.Products p
join (select ii.product_id, count(*) orderqty from dbo.Inventory ii where ii.product_id = p.product_id ) as i
on i.Product_id = p.product_id
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
robynumAuthor Commented:
Cool...Can I trouble you for two more questions?

1) I have a field that displays Date and Time (ex...3/9/2006 12:04:00 PM) I want to limit it to only show the Date. How?
2) I have another that shows my numbers out to a large number of Decimal places (ex...15.235432) I do not want to show the Decimals. How do I limit it?

Thanks,
R
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
1) use the convert(varchar(10), yourdatefield, xxxx) , where xxx is a style value, for example 101 (check out the convert and cast function in the books online about the possible values)
2) use the convert(numeric(10), yournumericfield) function, or to limit to 2 digits: convert(numeric(12,2), yournumericfield)
0
 
robynumAuthor Commented:
Cool...Thanks :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now