SQL query, if value is null then return 1

Question

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

See the [`ISNULL`](http://msdn.microsoft.com/en-us/library/ms184325.aspx) documentation (read: use it how it was designed) .. — , Feb 19 '13 at 16:55
@pst `COALESCE` is a SQL Standard and is generally recommended in these cases. also there are significant differences in how they respond to data types. perhaps you should caveat your comment with such additional information. — swasheck, Feb 19 '13 at 17:36
@swasheck The documentation does that. It also links to COALESCE. — , Feb 19 '13 at 17:54

score 143 · Accepted Answer · answered Feb 19 '13 at 16:53

143

You can use a CASE statement.

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

answered Feb 19 '13 at 16:53

Justin Helgerson

24,900
17
97
124

1

@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". – Feb 19 '13 at 17:17
3

@pst perhaps you can generate an execution plan and tell us how it's different, no? the downvote really isn't called-for, though. – swasheck Feb 19 '13 at 17:29
4

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. – AakashM Feb 19 '13 at 17:30
@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. – swasheck Feb 19 '13 at 17:33
@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... – AakashM Feb 19 '13 at 17:36
1

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? – jenhil34 Feb 19 '13 at 18:42
10

`Select IsNull(currate.currentrate, 1)` is a better answer, that's all. – Dominic Goulet Feb 19 '13 at 19:19
@pst [This article](http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/) will explain that this will produce an identical plan as `ISNULL` and `COALESCE`. – Kermit Feb 19 '13 at 19:20
@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. – Feb 19 '13 at 20:25
4

@pst *"I don't buy that"* doesn't sound like you're eliciting a complete answer. That's my 2 cents. – Kermit Feb 19 '13 at 20:46
1

@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. – swasheck Feb 19 '13 at 20:50
@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. – Feb 19 '13 at 21:02
@pst I would not be able to tell since your first comment was edited twice. – Kermit Feb 19 '13 at 21:04
"While this could be done, why do it this way? [The result is `currentrate` unless it is NULL.]" – Feb 19 '13 at 21:05

Taryn · Answer 2 · 2013-02-19T17:47:42.217

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/

Useful link, but it would be better if the answer included those points. — Max Barraclough, Oct 08 '20 at 16:54

score 18 · Answer 3 · answered May 31 '16 at 16:31

18

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

answered May 31 '16 at 16:31

Barka

8,764
15
64
91

score 9 · Answer 4 · edited May 16 '17 at 12:21

9

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

edited May 16 '17 at 12:21

Alexander

4,420
7
27
42

answered May 16 '17 at 11:18

bhavi

91
1
3

score 2 · Answer 5 · answered Feb 19 '13 at 16:55

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

score 0 · Answer 6 · answered Feb 19 '13 at 16:56

0

try like below...

CASE 
WHEN currate.currentrate is null THEN 1
ELSE currate.currentrate
END as currentrate

answered Feb 19 '13 at 16:56

Pandian

8,848
2
23
33

SQL query, if value is null then return 1

6 Answers6

Linked