When you want to replace a possibly null column with something else, use IsNull.
SELECT ISNULL(myColumn, 0 ) FROM myTable
This will put a 0 in myColumn if it is null in the first place.
Answer from phadaphunk on Stack OverflowWhen you want to replace a possibly null column with something else, use IsNull.
SELECT ISNULL(myColumn, 0 ) FROM myTable
This will put a 0 in myColumn if it is null in the first place.
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():
The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.
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.
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.
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
);
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.
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.
SQL case 0 then NULL - Stack Overflow
Replace null count with a zero (0)
Changing NULL Result to Zero in SubQuery ?
IF NULL then Zero (0) – SQLServerCentral Forums
Videos
As others have mentioned you forgot to tell your CASE statement to return the number in case the number is not null.
However in SQL Server you can use NULLIF, which I consider more readable:
select
id,
firstname,
lastname,
nullif(number, 0) as number
from tperson;
If you want to stick to standard SQL then stay with CASE:
case when number = 0 then null else number end as number
SELECT ID, Firstname, Lastname,
CASE WHEN Number!=0 THEN Number END
FROM tPerson
Hello,
This is my query
SELECT ordertype, status, sum (COUNT (printdate)) over (), date(char(1900000+requestdate)), businessunit FROM Casepallet WHERE date(char(1900000+requestdate)) IN (current date, current date + 1 days) AND business unit=' SCS' AND ordertype!='T1' AND status = 'X' group by ordertype, business unit, request date, status, printdate order by printdate desc limit 1
Sometimes the sum/count of printdate returns nothing, it's null. If it's null I want it to return 0.
I tried doing it like this: COALESCE (sum (COUNT (printdate)) over (), 0), but it doesn't work, it still returns null.
Can anyone guide me here on where I go wrong?
I am working on a query where I need to try and return the date column as Null even if it has a date in it.
The query needs to search 'status' for '5' then returns the column end date as null if it is 5 and leave it if it isn't 5.
I have a tried a few different ideas and haven't managed to get them to work wondering if anyone else might be aware on how to do this?
Use max(coalesce(logincount, 0)) to avoid NULLs
According to Postgres docs (9.6):
The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display, for example:
Maybe I've missed something obvious here, but wouldn't the NULLIF function be a much better (and more elegant) solution?
Postgres documentation here.
SELECT
COUNT(NULLIF(logincount,0)) AS "Max Login Count",
username,
FROM Test
GROUP BY username
ORDER BY "Max Login Count";
Result:
'Max Login Count' username
----------------------------
0 ff12
1 jb88
2 er11
You do realize you are returning multiple data types in your query? This is not very good query design if the result is a query table.
The data types for 1 and 0 is integer (whether it is int, smallint, or tinyint) while data type for ‘’ is character.
You also need to define what ‘Blank’ is since this is not a proper value definition in MS SQL. I am going to assume that you want ‘Blank’ to be the same as ‘’ which is a zero-length string of type character (whether it is char, varchar, nchar, or nvarchar).
Another common problem is the use of ‘= NULL’ instead of the proper form ‘IS NULL’ since NULL itself is not a value; in simple terms NULL is the absence of a value.
You should read another post here by Larry Shanahan and his included linked post from Robert Sheldon:
Returning a NULL Value when query returns nothing
Post by Robert Sheldon re NULLs
Back to your SQL code - I believe a better simpler version would be one that avoids the use of NULL altogether and would be the following; this also keeps all returned types as character; very close to your 2nd query version:
CASE
WHEN PAE.SEX = 'F' THEN '1'
WHEN PAE.SEX = 'M' THEN '0'
ELSE ''
END AS SEX,
@larryshanahan
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