SQL query, if value is null then return 1

tsql null ssms
jenhil34 picture jenhil34 · Feb 19, 2013 · Viewed 250.7k times · Source

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

Answer

Justin Helgerson picture Justin Helgerson · Feb 19, 2013

You can use a CASE statement.

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

Related questions

Altering a column: null to not null

I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to NOT NULL. Aside from changing nulls to 0, data must …

Read More
Not equal <> != operator on NULL

Could someone please explain the following behavior in SQL? SELECT * FROM MyTable WHERE MyColumn != NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn <> NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn IS NOT NULL (568 Results)

Read More
NULL values inside NOT IN clause

This issue came up when I got different records counts for what I thought were identical queries one using a not in where constraint and the other a left join. The table in the not in constraint had one null …

Read More