[tsql] 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

This question is related to tsql null ssms

The answer is


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  

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

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/


try like below...

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

Examples related to tsql

Passing multiple values for same variable in stored procedure Count the Number of Tables in a SQL Server Database Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Format number as percent in MS SQL Server EXEC sp_executesql with multiple parameters SQL Server after update trigger How to compare datetime with only date in SQL Server Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Printing integer variable and string on same line in SQL

Examples related to null

getElementById in React Filter values only if not null using lambda in Java8 Why use Optional.of over Optional.ofNullable? How to resolve TypeError: Cannot convert undefined or null to object Check if returned value is not null and if so assign it, in one line, with one method call How do I assign a null value to a variable in PowerShell? Using COALESCE to handle NULL values in PostgreSQL How to check a Long for null in java Check if AJAX response data is empty/blank/null/undefined/0 Best way to check for "empty or null value"

Examples related to ssms

The backend version is not supported to design database diagrams or tables How to export all data from table to an insertable sql format? Insert Data Into Temp Table with Query Incorrect syntax near '' How to find server name of SQL Server Management Studio Recover unsaved SQL query scripts SQL query, if value is null then return 1 How to connect to LocalDb How do you view ALL text from an ntext or nvarchar(max) in SSMS? How to install SQL Server Management Studio 2012 (SSMS) Express?