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

User · Accepted Answer

You can use a CASE statement   SELECT      CASE WHEN currate currentrate IS NULL THEN 1 ELSE currate currentrate END FROM

User · Answer

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

User · Answer

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

User · Answer

try like below     CASE  WHEN currate currentrate is null THEN 1 ELSE currate currentrate END as currentrate

User · Answer

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

User · Answer

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

[tsql] SQL query, if value is null then return 1

Examples related to tsql

Examples related to null

Examples related to ssms