isaacr25
asked on
Help with Not Equal SQL Statement
I have the following code as part of one of my sql statements. I'm trying to account for all values in the field that are not equal to Yes, including Null values. For some reason, if the field has <NULL> in it, the statement doesn't work. If the field has 'No' or some other value besides 'Yes' it works fine. Do I need to change something to find Nulls as well? I was under the impression that <> 'Yes' would work. Please advise. Thanks!
......
AND [Has__bAPI__bMatch__bRun__ Q]<> 'Yes'
......
AND [Has__bAPI__bMatch__bRun__
ASKER
I thought about that, but take a look at the entire statement:
UPDATE t1
SET API__bNumber = t2.[EMP ID]
FROM MASTER55 t1
JOIN [API].LaborWorkx_Live.dbo. vwCGHS_All _EmpList t2
ON t1.SSN = right(t2.SSN, len(t2.SSN) - 1)
AND t1.L__d__bName = t2.[Last Name]
AND t1.mrREF_TO_MR Is null
Is there anyway to group the two parts to your suggestion so that its not interpreted as --all of these previous statments--- OR Has__bAPI__bMatch__bRun__Q IS NULL)?
Does this make sense? Thanks.
UPDATE t1
SET API__bNumber = t2.[EMP ID]
FROM MASTER55 t1
JOIN [API].LaborWorkx_Live.dbo.
ON t1.SSN = right(t2.SSN, len(t2.SSN) - 1)
AND t1.L__d__bName = t2.[Last Name]
AND t1.mrREF_TO_MR Is null
Is there anyway to group the two parts to your suggestion so that its not interpreted as --all of these previous statments--- OR Has__bAPI__bMatch__bRun__Q
Does this make sense? Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I am a bit confused. I do not see a relationship between this:
AND ( [Has__bAPI__bMatch__bRun__ Q]<> 'Yes' OR Has__bAPI__bMatch__bRun__Q IS NULL)
and this:
UPDATE t1
SET API__bNumber = t2.[EMP ID]
FROM MASTER55 t1
JOIN [API].LaborWorkx_Live.dbo. vwCGHS_All _EmpList t2
ON t1.SSN = right(t2.SSN, len(t2.SSN) - 1)
AND t1.L__d__bName = t2.[Last Name]
AND t1.mrREF_TO_MR Is null
Do you want to add that predicate to the join criteria? If so, to which table does Has__bAPI__bMatch__bRun__Q belong?
Jim
AND ( [Has__bAPI__bMatch__bRun__
and this:
UPDATE t1
SET API__bNumber = t2.[EMP ID]
FROM MASTER55 t1
JOIN [API].LaborWorkx_Live.dbo.
ON t1.SSN = right(t2.SSN, len(t2.SSN) - 1)
AND t1.L__d__bName = t2.[Last Name]
AND t1.mrREF_TO_MR Is null
Do you want to add that predicate to the join criteria? If so, to which table does Has__bAPI__bMatch__bRun__Q
Jim
ASKER
Hi Jim,
Yes, I want to add the statement to the end of the larger statement. This field belongs to MASTER55.
Yes, I want to add the statement to the end of the larger statement. This field belongs to MASTER55.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> That being said, the database engine takes the most conservative rout and simply returns false when comparing a NULL value to 'Yes' <<
That is simply not true. If it returned "False", then this would return "True":
WHERE NOT (columnA <> 'Yes')
when columnA is NULL, but it is not. Instead, it also returns "NULL" (unknown), which is also not true for the purposes of a WHERE clause.
That is simply not true. If it returned "False", then this would return "True":
WHERE NOT (columnA <> 'Yes')
when columnA is NULL, but it is not. Instead, it also returns "NULL" (unknown), which is also not true for the purposes of a WHERE clause.
ASKER
As simple as parentheses... imagine that!
AND ( [Has__bAPI__bMatch__bRun__
Jim