SQL query, if value is null then return 1

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
303848 次浏览
小开
最佳答案

You can use a CASE statement.

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

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/

小开 
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
小开

try like below...

CASE
WHEN currate.currentrate is null THEN 1
ELSE currate.currentrate
END as currentrate
小开 
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

小开

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

提交答案