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/

You can use both of these methods but there are differences:

SELECT ISNULL(col1, 0 ) FROM table1
SELECT COALESCE(col1, 0 ) FROM table1

Comparing COALESCE() and ISNULL():

1. The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

2. Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times.

3. Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

4. The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different nullability values. This makes a difference if you are using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example.

-- This statement fails because the PRIMARY KEY cannot accept NULL values -- and the nullability of the COALESCE expression for col2 -- evaluates to NULL.

CREATE TABLE #Demo 
( 
    col1 integer NULL, 
    col2 AS COALESCE(col1, 0) PRIMARY KEY, 
    col3 AS ISNULL(col1, 0) 
); 

-- This statement succeeds because the nullability of the -- ISNULL function evaluates AS NOT NULL.

CREATE TABLE #Demo 
( 
    col1 integer NULL, 
    col2 AS COALESCE(col1, 0), 
    col3 AS ISNULL(col1, 0) PRIMARY KEY 
);

5. Validations for ISNULL and COALESCE are also different. For example, a NULL value for ISNULL is converted to int whereas for COALESCE, you must provide a data type.

6. ISNULL takes only 2 parameters whereas COALESCE takes a variable number of parameters.

   if you need to know more here is the full document from msdn.

You can use either NVL() or COALESCE().

SELECT NVL(NULL, 0) from dual;
SELECT COALESCE(NULL, 0) from dual;

NVL will return either the non-null value or, if it is null then the second parameter.

COALESCE allows for multiple parameters and will evaluate to the first non-null value in the parameter list.

SELECT c.name, IF(a.addressid IS NULL,0,1) AS addressexists
FROM customers c
LEFT JOIN addresses a ON c.customerid = a.customerid
WHERE customerid = 123

I need to return blank results when there is not a value entered into the table but have only been able to get either a null or 0 value.

CASE

WHEN PAE.SEX = ‘F’ THEN 1
WHEN PAE.SEX = ‘M’ THEN 0
WHEN PAE.SEX = NULL THEN ‘’
END AS SEX,

this returns a null value

CASE

WHEN PAE.SEX = ‘F’ THEN 1
WHEN PAE.SEX = ‘M’ THEN 0
ELSE ‘’
END AS SEX,

this returns a 0 value

The problem is likely the comparison to NULL, as explained in David Spillett's answer above. When @UserRole = 'Analyst', the comparison SupervisorApprovedBy = NULL will give UNKNOWN (and the row won't pass the WHERE test).

You can rewrite with nested CASE expressions:

WHERE 1 =
  CASE 
    WHEN @UserRole = 'Analyst' THEN 
        CASE WHEN SupervisorApprovedBy IS NULL THEN 1 END
    WHEN SupervisorApprovedBy IS NOT NULL THEN 1
  END

Or a complicated CASE expression:

WHERE 1 =
  CASE 
    WHEN @UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL THEN 1
    WHEN @UserRole = 'Analyst' THEN 0
    WHEN SupervisorApprovedBy IS NOT NULL THEN 1
  END

or with a bit more easy to understand AND / OR:

WHERE 
    @UserRole = 'Analyst'  AND SupervisorApprovedBy IS NULL 
 OR @UserRole <> 'Analyst' AND SupervisorApprovedBy IS NOT NULL 
 OR @UserRole IS NULL      AND SupervisorApprovedBy IS NOT NULL 

Another issue is that SupervisorApprovedBy = SupervisorApprovedBy (and the equivalent SupervisorApprovedBy IS NOT NULL I used above) will not give you "all data". The rows where SupervisorApprovedBy is null will not be returned. If you do want them all, the conditions should be all adjusted:

WHERE 1 =
  CASE 
    WHEN @UserRole = 'Analyst' THEN 
        CASE WHEN SupervisorApprovedBy IS NULL THEN 1 END
    ELSE 1
  END


WHERE 1 =
  CASE 
    WHEN @UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL THEN 1
    WHEN @UserRole = 'Analyst' THEN 0
    ELSE 1
  END


WHERE 
    @UserRole = 'Analyst'  AND SupervisorApprovedBy IS NULL 
 OR @UserRole <> 'Analyst' 
 OR @UserRole IS NULL       

SELECT ID, Firstname, Lastname,
 CASE WHEN Number!=0 THEN Number END 
FROM tPerson