0

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.

3
Contributors
2
Replies
11
Views
2 Years
Discussion Span
Last Post by AleMonteiro
0

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)
0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.