May 14, 2009 at 11:07 am
Here is my query script. When I have the main part of my script uncommented, it does NOT turn all the records NULL--it just doesn't work AT ALL! However, when I try to isolate this one line to understand WHY it is not working, it turns everything NULL.
First I want to know WHY it doesn't work as expected, TURNING everything that was ORIGINALLY NULL to 'NA', and secondly, i want to know why it is NOW doing SOMETHING once i comment all the other code out but changes EVERYTHING to NULL.
This code seemed to have worked fine the other day on a different table.
Thanks!
UPDATE dbo.Student
SET EmergencyRelation = CASE
/* WHEN EmergencyRelation = 'Uncle' THEN 'U'
WHEN EmergencyRelation = 'Brother' THEN 'B'
WHEN EmergencyRelation = 'Sister' THEN 'S'
WHEN EmergencyRelation = 'Mother' THEN 'M'
WHEN EmergencyRelation = 'Mom' THEN 'M'
WHEN EmergencyRelation = 'Father' THEN 'F'
WHEN EmergencyRelation = 'Grandmother' THEN 'GM'
WHEN EmergencyRelation = 'Grandfather' THEN 'GF'
WHEN EmergencyRelation = 'Neighbor' THEN 'N'
WHEN EmergencyRelation = 'Friend' THEN 'FR'
WHEN EmergencyRelation = 'Cousin' THEN 'C'
WHEN EmergencyRelation = 'Caretaker' THEN 'CT'
WHEN EmergencyRelation = 'Caregiver' THEN 'CT'
WHEN EmergencyRelation = 'Babysitter' THEN 'BS'
WHEN EmergencyRelation = 'Godfather' THEN 'Gfa'
WHEN EmergencyRelation = 'Godmother' THEN 'Gmo'
WHEN EmergencyRelation = 'Niece' THEN 'NIE'
WHEN EmergencyRelation = 'Pastor' THEN 'PA'
WHEN EmergencyRelation LIKE 'Godp%' THEN 'Gmo'
WHEN EmergencyRelation LIKE 'Grandp%' THEN 'GM'
WHEN EmergencyRelation LIKE '%Aunt%' THEN 'A' */
WHEN EmergencyRelation IS NULL THEN 'NA' END
--ELSE 'NA' END --Comment out during testing.
WHERE EmergencyContact IS NOT NULL
May 14, 2009 at 11:11 am
WEll I've figured out why it is turning it NULL because I'm not returning anything.
I should have and ELSE that simply returns EmergencyRelation. But i still don't know why that line isn't working right.
May 14, 2009 at 11:21 am
The answer so far is that none of the cells are NULL.
Here is the difference from yesterday. Yesterday I imported the data from an Excel file. Today I imported the data from a Tab Delimited file. Do tab delimited files honor NULLS? I don't see why not.
May 14, 2009 at 11:23 am
Need the DDL (CREATE TABLE) for the table(s), sample data (in a readily consumable format), expected results based on the sample data, and your complete existing code that is failing.
Please read the first article I reference in my signature block below if you need help with this request. It provides step by step instruction on how to accomplish all of this.
May 14, 2009 at 11:43 am
Jacob Pressures (5/14/2009)
WEll I've figured out why it is turning it NULL because I'm not returning anything.I should have and ELSE that simply returns EmergencyRelation. But i still don't know why that line isn't working right.
The UPDATE statement:
UPDATE dbo.Student
SET EmergencyRelation = CASE WHEN EmergencyRelation IS NULL THEN 'NA' END
WHERE EmergencyContact IS NOT NULL
is equivalent to this statement:
UPDATE dbo.Student
SET EmergencyRelation = CASE WHEN EmergencyRelation IS NULL THEN 'NA' ELSE NULL END
WHERE EmergencyContact IS NOT NULL
Any CASE expression without an explicitly specified ELSE clause will implicitly include an ELSE NULL clause. And yes, the above UPDATE statements will update any non-NULL EmergencyRelation column value to NULL for rows where the EmergencyContact column value is not NULL.
May 14, 2009 at 11:49 am
Thanks! I'm going to look into this further. As stated above I was not expecting this. I will read your article. I just glanced at it but I have it saved for future reference. As soon as i get a minute. I'll read over it.
May 14, 2009 at 11:55 am
Any CASE expression without an explicitly specified ELSE clause will implicitly include an ELSE NULL clause.
Thanks Andrew, I didn't know that
May 14, 2009 at 12:20 pm
Jacob Pressures (5/14/2009)
Any CASE expression without an explicitly specified ELSE clause will implicitly include an ELSE NULL clause.
Thanks Andrew, I didn't know that
Which is why on your other thread I added the last line to your CASE statement (ELSE 'NA').
May 14, 2009 at 1:01 pm
Lynn, some of us are a little bit slow. Not all of us are privileged with common sense. I remember your ELSE correction, that is what eventually led to my figuring out the problem. But as a RULE it didn't sink in. lol! Jesus! Duh!
Here is a problem I'm having.
I have a data set that looks like the following. I changed the spaces into NULLs so they are actually NULLs now so as not to create confusion.
EmergencyContact, EmergencyRelation
Jean, Mother
NULL, Father
Ted, NULL
NULL, NULL
I have always understood that if I use AND it means BOTH have to be true for the clause to evaluate as TRUE. Well, I changed the WHERE clause to
WHERE EmergencyRelation IS NOT NULL AND EmergencyContact IS NOT NULL
What I understand this to mean is that BOTH would have to be NULL for the record to be rejected or excluded. The second record should be changed to F for Father since BOTH columns are not null. The last record should be the only one ignored. All the other records should be evaluated.
What is actually happening though is that IF EmergencyRelation is NULL it ignores changing EmergencyContact. To me that would be an OR statement.
According to the code below, I'm getting these results:
EmergencyContact, EmergencyRelation
Jean, M
NULL, Father
Ted, NA
NULL, NULL
UPDATE dbo.Student
SET EmergencyRelation = (CASE
WHEN EmergencyRelation = 'Uncle' THEN 'U'
WHEN EmergencyRelation = 'Brother' THEN 'B'
WHEN EmergencyRelation = 'Sister' THEN 'S'
WHEN EmergencyRelation = 'Mother' THEN 'M'
WHEN EmergencyRelation = 'Mom' THEN 'M'
WHEN EmergencyRelation = 'Father' THEN 'F'
WHEN EmergencyRelation = 'Grandmother' THEN 'GM'
WHEN EmergencyRelation = 'Grandfather' THEN 'GF'
WHEN EmergencyRelation = 'Neighbor' THEN 'N'
WHEN EmergencyRelation = 'Friend' THEN 'FR'
WHEN EmergencyRelation = 'Cousin' THEN 'C'
WHEN EmergencyRelation = 'Caretaker' THEN 'CT'
WHEN EmergencyRelation = 'Caregiver' THEN 'CT'
WHEN EmergencyRelation = 'Babysitter' THEN 'BS'
WHEN EmergencyRelation = 'Godfather' THEN 'Gfa'
WHEN EmergencyRelation = 'Godmother' THEN 'Gmo'
WHEN EmergencyRelation = 'Niece' THEN 'NIE'
WHEN EmergencyRelation = 'Pastor' THEN 'PA'
WHEN EmergencyRelation LIKE 'Godp%' THEN 'Gmo'
WHEN EmergencyRelation LIKE 'Grandp%' THEN 'GM'
WHEN EmergencyRelation LIKE '%Aunt%' THEN 'A'
WHEN EmergencyRelation IS NULL THEN 'NA'
WHEN EmergencyRelation = ' ' THEN 'NA'
ELSE 'NA' END) --Comment out during testing.
WHERE EmergencyRelation IS NOT NULL AND EmergencyContact IS NOT NULL
May 14, 2009 at 1:24 pm
Jacob Pressures (5/14/2009)
Lynn, some of us are a little bit slow. Not all of us are privileged with common sense. I remember your ELSE correction, that is what eventually led to my figuring out the problem. But as a RULE it didn't sink in. lol! Jesus! Duh!Here is a problem I'm having.
I have a data set that looks like the following. I changed the spaces into NULLs so they are actually NULLs now so as not to create confusion.
EmergencyContact, EmergencyRelation
Jean, Mother
NULL, Father
Ted, NULL
NULL, NULL
I have always understood that if I use AND it means BOTH have to be true for the clause to evaluate as TRUE. Well, I changed the WHERE clause to
WHERE EmergencyRelation IS NOT NULL AND EmergencyContact IS NOT NULL
What I understand this to mean is that BOTH would have to be NULL for the record to be rejected or excluded. The second record should be changed to F for Father since BOTH columns are not null. The last record should be the only one ignored. All the other records should be evaluated.
What is actually happening though is that IF EmergencyRelation is NULL it ignores changing EmergencyContact. To me that would be an OR statement.
According to the code below, I'm getting these results:
EmergencyContact, EmergencyRelation
Jean, M
NULL, Father
Ted, NA
NULL, NULL
UPDATE dbo.Student
SET EmergencyRelation = (CASE
WHEN EmergencyRelation = 'Uncle' THEN 'U'
WHEN EmergencyRelation = 'Brother' THEN 'B'
WHEN EmergencyRelation = 'Sister' THEN 'S'
WHEN EmergencyRelation = 'Mother' THEN 'M'
WHEN EmergencyRelation = 'Mom' THEN 'M'
WHEN EmergencyRelation = 'Father' THEN 'F'
WHEN EmergencyRelation = 'Grandmother' THEN 'GM'
WHEN EmergencyRelation = 'Grandfather' THEN 'GF'
WHEN EmergencyRelation = 'Neighbor' THEN 'N'
WHEN EmergencyRelation = 'Friend' THEN 'FR'
WHEN EmergencyRelation = 'Cousin' THEN 'C'
WHEN EmergencyRelation = 'Caretaker' THEN 'CT'
WHEN EmergencyRelation = 'Caregiver' THEN 'CT'
WHEN EmergencyRelation = 'Babysitter' THEN 'BS'
WHEN EmergencyRelation = 'Godfather' THEN 'Gfa'
WHEN EmergencyRelation = 'Godmother' THEN 'Gmo'
WHEN EmergencyRelation = 'Niece' THEN 'NIE'
WHEN EmergencyRelation = 'Pastor' THEN 'PA'
WHEN EmergencyRelation LIKE 'Godp%' THEN 'Gmo'
WHEN EmergencyRelation LIKE 'Grandp%' THEN 'GM'
WHEN EmergencyRelation LIKE '%Aunt%' THEN 'A'
WHEN EmergencyRelation IS NULL THEN 'NA'
WHEN EmergencyRelation = ' ' THEN 'NA'
ELSE 'NA' END) --Comment out during testing.
WHERE EmergencyRelation IS NOT NULL AND EmergencyContact IS NOT NULL
Nope. You are using IS NOT NULL, so the AND should be an OR. Think of it this way:
WHERE NOT (EmergencyRelation IS NULL AND EmergencyContact IS NULL)
Boolean logic can be somewhat confusing at first.
May 14, 2009 at 1:42 pm
De Morgan's laws:
1)
IF (NOT A=B) OR (NOT C=D)
is the same as
IF NOT ( A=B AND C=D)
2)
IF (NOT A=B) AND (NOT C=D)
is the same as
IF NOT ( A=B OR C=D)
(actually the "=" part is more limiting that the actual laws, but is easier for most folks to understand).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 14, 2009 at 2:04 pm
I think Lynn covered it, but maybe this sample would help to demonstate:DECLARE @Foo TABLE(EmergencyContact VARCHAR(50), EmergencyRelation VARCHAR(50))
INSERT @Foo
SELECT 'Jean', 'Mother'
UNION ALL SELECT NULL, 'Father'
UNION ALL SELECT 'Ted', NULL
UNION ALL SELECT NULL, NULL
SELECT
EmergencyContact,
EmergencyRelation,
CASE WHEN EmergencyContact IS NULL THEN 1 ELSE 0 END AS EmergencyContactIsNull,
CASE WHEN EmergencyRelation IS NULL THEN 1 ELSE 0 END AS EmergencyRelationIsNull,
CASE WHEN EmergencyRelation IS NOT NULL AND EmergencyContact IS NOT NULL THEN 1 ELSE 0 END AS BothColumnsNotNull,
CASE WHEN EmergencyRelation IS NOT NULL OR EmergencyContact IS NOT NULL THEN 1 ELSE 0 END AS EitherColumnsNotNull
FROM
May 14, 2009 at 3:39 pm
I started off in University as EE Major and had boolean logic hammered into me in several microprocessor design classes where we worked closely with AND gates, OR gates, NAND gates, NOR gates, etc. Learning how to build even simple systems took quite a bit of work at first to get things right.
May 14, 2009 at 4:13 pm
I don't think I've ever encountered anything like this! Makes me wonder what else I wasn't taught in school.
I'm going to try this code out. Thanks for the example up above and read up on this a little more.
Thanks guys!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply