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.


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

SELECT column



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

    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 

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

This article has been dead for over six months. Start a new discussion instead.