New answers tagged null
0
There is a class of problems for which the empty string is useful, and carries meaning that NULL specifically does not.
The empty string is the identity under the concatenate operator. If you concatenate the empty string with any string you get the same string back. If your application manipulates strings in this way, then reserving the empty string for a ...
1
Dump isnulls into an inner query and use them outside maybe to avoid multiple isnulls?
SELECT A
FROM (SELECT ISNULL (A, 0) FROM TABLEA) A
...
1
If you are only expecting one or zero rows back, then this would also work:
SELECT
max(col1) col1,
max(col2) col2,
1 AS query_id
FROM
players
WHERE
username='foobar';
This will return one row with all values having null except query_id if no row is found.
4
SELECT col1,
col2,
col3,
1 AS query_id
FROM players
WHERE username='foobar'
union all
select null,
null,
null,
1
where not exists (select 1 from players where username = 'foobar');
Or as an alternative (might be faster as no second subselect is required):
with qid (query_id) as (
values (1)
)
select p.*, ...
1
The only option that comes to mind is a BEFORE INSERT trigger. In 5.5 you could use SIGNAL:
DELIMITER $$
CREATE TRIGGER bi_foo BEFORE INSERT on foo
FOR EACH ROW
BEGIN
IF (ISNULL(NEW.col3) AND (SELECT COUNT(*) FROM foo
WHERE col1 = NEW.col1 AND col2 = NEW.col2 AND col3 IS NULL) > 0)
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = ...
3
I have a similar query showing a TON of encapsulating ISNULL statements. I need the null values to represent items that have not been touched (as many columns required distinct identifiers for alterations). I tried a million different things to get around it and in the end just ended up with an annoyingly long code laced with a bunch of ISNULLs. The ...
6
No, there is no way to tell SQL Server to treat all NULL float values as zero. You will have to surround these expressions with ISNULL() or, better yet IMHO, COALESCE(). You can do this in a view so you don't have to repeat it in every query.
0
Let's say your query looks like this:
SELECT type,person,COUNT(*) `Count`
FROM mytable GROUP BY type,person WITH ROLLUP;
The NULL values coming out can be substituted with something like this...
SELECT
IFNULL(type,'All Types') Type,
IFNULL(person,'All Persons') Person
COUNT(*) `Count`
FROM mytable GROUP BY type,person WITH ROLLUP;
Using ...
2
Use an OR to check if either the parameter matches the value, or both the parameter and the value are NULL, like this:
WHERE
(
[TBL_OUTBOUND_REVIEW].[ERROR_FREE] = @Status
OR
([TBL_OUTBOUND_REVIEW].[ERROR_FREE] IS NULL AND @Status IS NULL)
)
You could also use a CASE statement as your IF if you really wanted to, but I think it's easier to ...
Top 50 recent answers are included
