CODEWITHSUNDEEP
sqlsql-servert-sql
Bill
Bill

Reputation: 1195

Coalesce equivalent for NULL or zero amount

What is the easiest way to select amountB for both test cases where amountA is null or 0?

DECLARE @amountA float;
DECLARE @amountB float = 3.33;
select coalesce(@amountA, @amountB)

SET @amountA  = 0.00;
select coalesce(@amountA, @amountB)

Upvotes: 1

Views: 847

Answers (4)

Rajat
Rajat

Reputation: 5803

You could nest case inside the coalesce. 

select coalesce(case when @amountA > 0 then @amountA end, @amountB)

Could also rewrite as 

select case when @amountA > 0 then @amountA else @amountB end

Note that unless you change the conditional operator above from > to <>, negative values will get assigned @amountB

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453018

I would invert the conditions in the CASE in the accepted answer and use IIF 

SELECT IIF(@amountA <> 0, @amountA, @amountB)

If @amountA is 0 or NULL the expression will evaluate to false or unknown respectively and both of those fall through to the ELSE branch.

Upvotes: 3

Ilyes
Ilyes

Reputation: 14928

Are you just looking for 

DECLARE @amountA float;
DECLARE @amountB float = 3.33;
select coalesce(nullif(@amountA, 0), @amountB);

SET @amountA  = 0.00;
select coalesce(nullif(@amountA, 0), @amountB);

Or using a CASE expression or IIF() function as I say in my comment.

Upvotes: 3

The Impaler
The Impaler

Reputation: 48770

For this case I would use CASE:

select case when @amountA is null or @amountA = 0 then @amountB else @amountA end

Upvotes: 4

Related Questions