SQL query, if value is null then return 1

tsql null ssms

280,659

Solution 1

You can use a CASE statement.

SELECT 
    CASE WHEN currate.currentrate IS NULL THEN 1 ELSE currate.currentrate END
FROM ...

Solution 2

You can use COALESCE:

SELECT  orderhed.ordernum, 
    orderhed.orderdate, 
    currrate.currencycode,  
    coalesce(currrate.currentrate, 1) as currentrate
FROM orderhed 
LEFT OUTER JOIN currrate 
    ON orderhed.company = currrate.company 
    AND orderhed.orderdate = currrate.effectivedate

Or even IsNull():

SELECT  orderhed.ordernum, 
    orderhed.orderdate, 
    currrate.currencycode,  
    IsNull(currrate.currentrate, 1) as currentrate
FROM orderhed 
LEFT OUTER JOIN currrate 
    ON orderhed.company = currrate.company 
    AND orderhed.orderdate = currrate.effectivedate

Here is an article to help decide between COALESCE and IsNull:

http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/

Solution 3

SELECT 
    ISNULL(currate.currentrate, 1)  
FROM ...

is less verbose than the winning answer and does the same thing

https://msdn.microsoft.com/en-us/library/ms184325.aspx

Solution 4

a) If you want 0 when value is null

SELECT isnull(PartNum,0) AS PartNumber, PartID
FROM Part

b) If you want 0 when value is null and otherwise 1

SELECT 
  (CASE
    WHEN PartNum IS NULL THEN 0
    ELSE 1
  END) AS PartNumber,
  PartID
FROM Part

Solution 5

SELECT orderhed.ordernum, orderhed.orderdate, currrate.currencycode,  

case(currrate.currentrate) when null then 1 else currrate.currentrate end

FROM orderhed LEFT OUTER JOIN currrate ON orderhed.company = currrate.company AND orderhed.orderdate = currrate.effectivedate

View more solutions

280,659

Author by

jenhil34

Updated on July 05, 2022

Comments

jenhil34 almost 2 years
I have a query that is returning the exchange rate value set up in our system. Not every order will have an exchange rate (currate.currentrate) so it is returning null values.

Can I get it to return 1 instead of null?

Something like an if statement maybe:
```
 if isnull(currate.currentrate) then 1 else currate.currentrate 
```
Here is my query below. I greatly appreciate all your help!
```
 SELECT     orderhed.ordernum, orderhed.orderdate, currrate.currencycode,  currrate.currentrate
 FROM         orderhed LEFT OUTER JOIN
                  currrate ON orderhed.company = currrate.company AND orderhed.orderdate = currrate.effectivedate
```
Admin over 11 years

@Ek0nomik I acknowledged the equivalence above and I do not find that applying a down-vote in order to draw out a point or elicit a better explanation makes one an "Internet tough guy".
swasheck over 11 years

@pst perhaps you can generate an execution plan and tell us how it's different, no? the downvote really isn't called-for, though.
AakashM over 11 years

The next dev to read this is going to think "Why didn't they just use ISNULL or COALESCE?". Every second they spend trying to find out or work out why is going to be a second wasted.
swasheck over 11 years

@AakashM unless they look at this highest-upvoted answer here. if a dev just looks for a green checkmark then i'm not really sure i want him deploying their code into my environment.
AakashM over 11 years

@swasheck true, although what I meant was the next dev to look at this code where the asker uses it. This might of course be the asker themselves, somewhere down the line...
jenhil34 over 11 years

I'm just aking to learn more, but using the solution @ek0nomik provided worked correctly and quickly. I am sure as there are different ways in the end to make this work, but for my particaular application, is it really so unacceptable so many comments have been made about it? Am I missing the big picture?
Dominic Goulet over 11 years

Select IsNull(currate.currentrate, 1) is a better answer, that's all.
Kermit over 11 years

@pst This article will explain that this will produce an identical plan as ISNULL and COALESCE.
Admin over 11 years

@njk It's not a bout the plan here; my entire (and only) goal with my comments was to elicit a more complete answer - some deeper explanation or rationale. If someone had left such a comment on one of my answers - and there are many such answers of mine that for which such comments would be well justified - I would merely have updated with an appropriate justification / link / information / acknowledgement / rebuttal or outright deleted it. Stackoverflow is an "organic" resource that provides opportunities for growth and clarification.
Kermit over 11 years

@pst "I don't buy that" doesn't sound like you're eliciting a complete answer. That's my 2 cents.
swasheck over 11 years

@jenhil34 As with most things, "it depends." ISNULL and COALESCE achieve similar functionality to the CASE logic in this answer. You can reduce development time by using ISNULL and COALESCE. There are also other considerations with regards to data type precedence as well as how many parameters they can contain.
Admin over 11 years

@njk See my first comment which was all-but ignored. That language is a bit terse, and was reactionary to the irrelevant comment that it followed: reversing the order buys nothing here. Now, "CASE can apply a general mapping over many values", is different.
Kermit over 11 years

@pst I would not be able to tell since your first comment was edited twice.
Admin over 11 years

"While this could be done, why do it this way? [The result is currentrate unless it is NULL.]"
Max Barraclough over 3 years

Useful link, but it would be better if the answer included those points.