Search type Search syntax
Tags [tag]
Exact "words here"
Author user:1234
user:me (yours)
Score score:3 (3+)
score:0 (none)
Answers answers:3 (3+)
answers:0 (none)
Views views:250
Sections title:apples
body:"apples oranges"
URL url:"*"
Favorites infavorites:mine
Status closed:yes
Types is:question
Exclude -[tag]
For more details on advanced search visit our help page
Results tagged with Search options answers only user 993

A database concept used to represent missing, unknown or inapplicable data.

I think you need a LEFT JOIN or an inline subquery. The FROM b is also missing. For the NULL to be converted to 0, you need the equivalent of COALESCE() or ISNULL(), which is the Nz() function in …
answered Jul 1 '16 by ypercubeᵀᴹ
Your 2nd query seems correct and does what you want. Another option would be to use the TOP .. WITH TIES but this works only because NULL values are treated as the lowest in SQL Server. SELECT TOP …
answered May 12 '16 by ypercubeᵀᴹ
There are subtle differences on how UNKNOWN and NULL are treated in the various clauses. Three clauses where a boolean expression can appear are the WHERE, HAVING and FROM (in the ON sub-clause). In … , there is a more "relaxed" treatment for UNKNOWN. I guess this has to do with the decision of allowing nulls in the first place. A null value will result in an UNKNOWN result in most constraints, so …
answered Mar 9 '14 by ypercubeᵀᴹ
Equivalent and simpler: IF NEW.value IS DISTINCT FROM OLD.value THEN ... END IF;
answered Nov 3 '16 by ypercubeᵀᴹ
You are right, the code makes very little sense, in many places. This part in particular is completely redundant: ISNULL(bt.BusinessTypeID, NULL) You can use whatever in place of ISNULL … (whatever, NULL), no matter if whatever is nullable or not. The other part, ISNULL(bt.BusinessTypeID, 0) is also not needed but only because bt.BusinessTypeID is not nullable (it's the PK of the table) and …
answered Mar 23 '16 by ypercubeᵀᴹ
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 …
answered Aug 1 '17 by ypercubeᵀᴹ
First, let me explain why your code doesn't work when A or B is NULL by formatting your code to show that you have 2 CASE expressions, one inside the other: CF1 = (case when [A]>[B] then … A = B or A is null or B is null. So to solve the issue, you need to explicitly compare A = B or compare A and B with NULL, to differentiate. As a result the CASE expression should have 4 parts, not …
answered Dec 27 '16 by ypercubeᵀᴹ
Use a CHECK constraint. CHECK constraints do not fail when the value is null. If the value is null, the condition of the check constraints usually evaluates to UNKNOWN and the row is accepted. Only … the database. One problem exists though with the specific constraint, see below*. You case could be something like below. Tested at CREATE TABLE person ( person_id INT NOT NULL
answered Dec 16 '16 by ypercubeᵀᴹ
,c,d) when all the attributes are not null. And uniqueness on (a,b,c) when d is null. And uniqueness on (a,b,d) when c is null. And uniqueness on (a,b) when both c and d are null. You say that NULL … value on c or d means that the value is known to be undefined, yet you allow both (10, 20, 30, 40) and (10, 20, NULL, NULL). Is the value defined or known to be undefined in this case? Anyway, to …
answered May 15 '16 by ypercubeᵀᴹ
A Null can mean that value is not known for that row at the moment but will be added, when known, in the future (example FinishDate for a running Project) or that no value can be applied for that row … want only one NULL to be allowed. Why? What kind of relationship is this between the two tables? Perhaps you can simply change the column to NOT NULL and store, instead of NULL, a special value (like …
answered Dec 28 '11 by ypercubeᵀᴹ
After the 1st full join: ON co.group_name = requests.group_name there are possibly rows from either table (groups or requests) that don't match the other and the empty columns are filled with NULL … values. Then the 2nd full join happens: ON requests.group_name = problems.group_name but this ON condition will be satisfied only by rows that have non-null requests.group_name. This basically …
answered Jun 10 '16 by ypercubeᵀᴹ
Adding the column: alter table device_login add column person_id int ; The query is a bit complicated, due to the nulls. First group the not null logins per login and keep each null seprately … with a as ( select min(device_id) as device_id, login_id from device_login where login_id is not null group by login_id union all select device_id, login_id from device_login where …
answered Jun 13 '15 by ypercubeᵀᴹ
first 3 columns and NULL in the alias. The constraint is skipped / accepted when at least one value is null. MySQL documentation on CREATE TABLE is not very clear but you can test the behaviour: A … UNIQUE index permits multiple NULL values for columns that can contain NULL. What you could do is to define the alias as not null and add a default value, (say 'NONE' or 'DEFAULT' or the empty …
answered Aug 25 '15 by ypercubeᵀᴹ
This is by design. COUNT(<expression>) counts rows where the <expression> is not null. COUNT(*) counts rows. So, if you want to count rows, use COUNT(*). …
answered Oct 28 '16 by ypercubeᵀᴹ
you run it in the future, say next year when you have rows with 2016 and 2017. Second, the error is due to for some rows, either the subquery returns a NULL value or it return no rows at all. You …
answered Jun 14 '15 by ypercubeᵀᴹ

15 30 50 per page