using IsNull to get sum() in vb.net

[eetigu]

hi guys,

i have the following code:

"select sum(( receiptDetails.retailprice - receiptDetails.purchaseprice)* receiptDetails.itemcount ) + ((customerReceiptDetails.retailPrice -customerReceiptDetails.purchasePrice)* customerReceiptDetails.itemCount) As Total from receiptDetails,customerReceiptDetails "

am trying to get the gross profit from the two tables: receiptDetails and customerReceiptDetails. the query works well if both tables have values. i tried to use IsNull within the query to sovle the NULL problem but was not successful. any assistance will be highly appreciated.

thanks.

[Reverend Jim]

You could convert nulls to 0. In MS SQL it is the COALESCE function. When you specify a column, instead of

SELECT column

use

SELECT COALESCE(column,0)

[AleMonteiro]

eetigu, just adding IsNull to your own logic it would be something like this:

SELECT 
    SUM((IsNull(receiptDetails.retailprice, 0) - IsNull(receiptDetails.purchaseprice, 0)) * IsNull(receiptDetails.itemcount,0) ) + 
    ((IsNull(customerReceiptDetails.retailPrice, 0) - IsNull(customerReceiptDetails.purchasePrice, 0)) * isNull(customerReceiptDetails.itemCount, 0))
    As Total 
FROM 
    receiptDetails,customerReceiptDetails

And as Jim said, you can use COALESCE instead of IsNull.
The basic differences are: IsNull works only with 2 arguments and the returned type is the same as the default value(int for 0 in this case).
While Coalesce can be used with any number of arguments(it returns the first non null value) and the returned type will be that of first the non null element.

For further explanation when to use IsNull or Coalesce, here's a good resource: http://sqlmag.com/t-sql/coalesce-vs-isnull