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

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

Linq to sql with select case

Hi EE,

I am trying to evaluate numbers for 0 before I divide the numbers in the linq query.
I know how to do a select case in a stored procedure but can't figure out how to do it in code behind VB.Net.
How do I check numbers for values of 0?

Thanks

Dim q = (From a In dc.ProductDetails Where a.ProductDetailID = iItemID _
                          Select New curstock = if((a.OuncesInStock = 0,a.OuncesInStock,  0) / if(a.OuncesPerItem = 0, 1))).FirstOrDefault

Open in new window

0
Sheritlw
Asked:
Sheritlw
  • 3
  • 3
  • 2
1 Solution
 
käµfm³d 👽Commented:
You first "if" seems strange. No matter what, you will always be dividing into zero. Since zero can appear as a numerator, I've modded your query slightly. I'm on my Linux box right now, so I can't test, but see if this works for you:
Dim q = (From a In dc.ProductDetails _
		 Where a.ProductDetailID = iItemID _
		 Select New With { .curstock = a.OuncesInStock / IIF(a.OuncesPerItem = 0, 1, a.OuncesPerItem) }).FirstOrDefault()

Open in new window

0
 
SheritlwAuthor Commented:
Hi Kaufmed,

I received the error...
The IIF method returns two separate types: Int32, Decimal.  Translation to SQL does not support different return types.
I did change, 1 to 1.0 but I still got the error.  Both fields are of decimal type.
Any ideas?

Thanks
0
 
käµfm³d 👽Commented:
Try "1.0D":
Dim q = (From a In dc.ProductDetails _
		 Where a.ProductDetailID = iItemID _
		 Select New With { .curstock = a.OuncesInStock / IIF(a.OuncesPerItem = 0, 1.0D, a.OuncesPerItem) }).FirstOrDefault()

Open in new window

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
SheritlwAuthor Commented:
Hi Kaufmed,

I tried the 1.0D but it changed to 1D.
I then tried creating two decimal variables but I still get the same error.  I looked at the database and both values are 0.00.  The table allows 0 since it is an inventory table.
Dim decVal As Decimal = 1.0
 Dim decZero As Decimal = 0

 Dim q = (From a In dc.ProductDetails _
 Where a.ProductDetailID = iItemID _
 Select New With {.curstock = IIf(a.OuncesInStock = decZero, decVal, a.OuncesInStock) / IIf(a.OuncesPerItem = decZero, decVal, a.OuncesPerItem)}).FirstOrDefault()

Open in new window

0
 
käµfm³d 👽Commented:
Both fields are of decimal type.
When you say "fields" are you referring to the DB columns, the type declaration of the Linq tables' columns, or both?
0
 
SheritlwAuthor Commented:
Fields are the columns in the database.  Both OuncesInStock and OuncesPerItem are of dbtype decimal.
Normally I can divide ouncesinstock by ouncesperite.

hmmm just a thought, maybe I should do a where clause specifying that both ouncesinstock and ouncesperitem > 0,  if not wouldn't q return nothing?

Thanks
0
 
Fernando SotoCommented:
Hi Sheritlw;

The IIf method return an Object type convert it to Decimal before using it, see code snippet.

Dim q = (From a In dc.ProductDetails _
		 Where a.ProductDetailID = iItemID _
		 Select New With { .curstock = a.OuncesInStock / Convert.ToDecimal(IIF(a.OuncesPerItem = 0, 1.0D, a.OuncesPerItem)) }).FirstOrDefault()

Open in new window


Fernando
0
 
Fernando SotoCommented:
So it excepted an object in place of a Decimal?
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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