CODEWITHSUNDEEP
sqlmysql
Night Walker
Night Walker

Reputation: 21260

check that values not null

How I can rewrite following query that I will take minus Correction and Used only when they are not null. 

select Amount - Correction - Used 
    from CompList 
    where (CompID ='D999999' and  Amount IS NOT NULL) ;

Thanks for help.

Upvotes: 0

Views: 115

Answers (2)

A.Baudouin
A.Baudouin

Reputation: 2925

You can also use the function IFNULL(field, 0). It could be faster than COALESCE : you must test ...

Upvotes: 1

MikeTheReader
MikeTheReader

Reputation: 4190

Use the COALESCE function (which returns the first non-null value):

select Amount - COALESCE(Correction, 0) - COALESCE(Used, 0) ....

Upvotes: 3

Related Questions