[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

How to do a multiplication if a field is NULL

I got a query that does a summary, thing is, one of the fields is an LEFT JOIN thus can return NULL in some cases, how do I handle this?
The particular field looks like this:

PRICE.PRICEOUT * (100 - DISC.DISC) / 100 / PROD.QUANTITY AS finalprice

DISC.DISC can be NULL (actually, its mostly null) :P
I'd like NULL to be 0.
Doing some google, IFNULL(expression, substitute) turns up, but it seems to be mysql, is there an equiveland for MSSQL?
0
Squeese
Asked:
Squeese
  • 2
1 Solution
 
RiteshShahCommented:
PRICE.PRICEOUT * (100 - isnull(DISC.DISC,1)) / 100 / PROD.QUANTITY AS finalprice
0
 
chapmandewCommented:
not quite right...should be 0 instead of 1

PRICE.PRICEOUT * (100 - isnull(DISC.DISC,0)) / 100 / PROD.QUANTITY AS finalprice
0
 
Patrick MatthewsCommented:
In addition to ISNULL, you can use COALESCE, which returns the first non-null value in the argument list:

PRICE.PRICEOUT * (100 - COALESCE(DISC.DISC, 0)) / 100 / PROD.QUANTITY AS finalprice
0
 
RiteshShahCommented:
yes chapman, I put 1 instead 0 in hurry
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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