How to do a multiplication if a field is NULL

Posted on 2009-04-17
Last Modified: 2013-11-05
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?
Question by:Squeese
    LVL 31

    Accepted Solution

    PRICE.PRICEOUT * (100 - isnull(DISC.DISC,1)) / 100 / PROD.QUANTITY AS finalprice
    LVL 60

    Expert Comment

    not quite right...should be 0 instead of 1

    PRICE.PRICEOUT * (100 - isnull(DISC.DISC,0)) / 100 / PROD.QUANTITY AS finalprice
    LVL 92

    Expert Comment

    by:Patrick Matthews
    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
    LVL 31

    Expert Comment

    yes chapman, I put 1 instead 0 in hurry

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now