January 5, 2017 at 4:41 am
Brandie Tarvin (1/5/2017)
Rasmus Remmer Bielidt (1/5/2017)
Both options have their uses for sure.I didn't see anybody making this point, but COALESCE() absolutely needs a non-null argument or it will throw an error where a case without a "hit" will use the ELSE clause (or null where applicable).
COALESCE(NULL,NULL)will get you
Msg 4127, Level 16, State 1, Line 1
At least one of the arguments to COALESCE must be an expression that is not the NULL constant.
At least CASE let's you specify an ELSE for "none of the above".
Indeed - as long you have the same number of ELSE as you have CASE 🙂
January 5, 2017 at 4:49 am
Rasmus Remmer Bielidt (1/5/2017)
Brandie Tarvin (1/5/2017)
Rasmus Remmer Bielidt (1/5/2017)
Both options have their uses for sure.I didn't see anybody making this point, but COALESCE() absolutely needs a non-null argument or it will throw an error where a case without a "hit" will use the ELSE clause (or null where applicable).
COALESCE(NULL,NULL)will get you
Msg 4127, Level 16, State 1, Line 1
At least one of the arguments to COALESCE must be an expression that is not the NULL constant.
At least CASE let's you specify an ELSE for "none of the above".
Indeed - as long you have the same number of ELSE as you have CASE 🙂
<blink> Huh?
I'm not sure I understand what you mean by that. Could you clarify?
January 5, 2017 at 5:00 am
I've had to troubleshoot queries where the developer had forgotten an ELSE clause and the whole thing returned NULL (which was not expected).
this will yield NULL due to not having an ELSE clause.
SELECT CASE 1 WHEN 0 THEN 'NON-NULL' END AS "NONSENSE"
January 5, 2017 at 5:08 am
Rasmus Remmer Bielidt (1/5/2017)
I've had to troubleshoot queries where the developer had forgotten an ELSE clause and the whole thing returned NULL (which was not expected).this will yield NULL due to not having an ELSE clause.
SELECT CASE 1 WHEN 0 THEN 'NON-NULL' END AS "NONSENSE"
Oh. Okay. The way you worded it, I first read it as "one ELSE per switch (WHEN) in the CASE statement". I agree that every CASE should have an ELSE. As should an IF.
January 5, 2017 at 7:16 am
Rasmus Remmer Bielidt (1/5/2017)
Both options have their uses for sure.I didn't see anybody making this point, but COALESCE() absolutely needs a non-null argument or it will throw an error where a case without a "hit" will use the ELSE clause (or null where applicable).
COALESCE(NULL,NULL)will get you
Msg 4127, Level 16, State 1, Line 1
At least one of the arguments to COALESCE must be an expression that is not the NULL constant.
That's because it's nonsense to write COALESCE(NULL,NULL) or basically any other version of the COALESCE function using a NULL constant.
The error won't appear when there's a NULL that comes from a column or variable. The reason is that it will have a type for the result of the function.
The code you shared is basically this:
SELECT CASE WHEN NULL IS NULL THEN NULL ELSE NULL END;
January 5, 2017 at 8:38 am
Luis Cazares (1/5/2017)
Rasmus Remmer Bielidt (1/5/2017)
Both options have their uses for sure.I didn't see anybody making this point, but COALESCE() absolutely needs a non-null argument or it will throw an error where a case without a "hit" will use the ELSE clause (or null where applicable).
COALESCE(NULL,NULL)will get you
Msg 4127, Level 16, State 1, Line 1
At least one of the arguments to COALESCE must be an expression that is not the NULL constant.
That's because it's nonsense to write COALESCE(NULL,NULL) or basically any other version of the COALESCE function using a NULL constant.
The error won't appear when there's a NULL that comes from a column or variable. The reason is that it will have a type for the result of the function.
As Luis said, it's not because the arguments are NULL that's the problem, it's that it can't determine the data type. All of the following work, even though every single one of the arguments is null.
DECLARE @i INT, @j-2 INT
SELECT COALESCE(@i, @j-2), @i, @j-2
SELECT COALESCE(@i, NULL)
SELECT COALESCE(NULL, @j-2)
SELECT COALESCE(CAST(NULL AS INT), NULL)
The code you shared is basically this:
SELECT CASE WHEN NULL IS NULL THEN NULL ELSE NULL END;
You forgot the NOT.
SELECT CASE WHEN NULL IS NOT NULL THEN NULL ELSE NULL END;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 9, 2017 at 6:46 pm
You forgot the NOT.
SELECT CASE WHEN NULL IS NOT NULL THEN NULL ELSE NULL END;
How about
SELECT CASE WHEN NULL IS NOT NULL THEN NOT NULL ELSE NULL END
?
same logical result to the original expression COALESCE(null, null) . Though if you keep going these nulls can start to become a head spinner.
----------------------------------------------------
January 9, 2017 at 10:11 pm
MMartin1 (1/9/2017)
You forgot the NOT.
SELECT CASE WHEN NULL IS NOT NULL THEN NULL ELSE NULL END;
How about
SELECT CASE WHEN NULL IS NOT NULL THEN NOT NULL ELSE NULL END
?
same logical result to the original expression COALESCE(null, null) . Though if you keep going these nulls can start to become a head spinner.
It's rather SELECT CASE WHEN NULL = NULL THEN NULL ELSE NULL END
Fails the same way as COALESCE.
Which proves - it's an adequate replacement.
:hehe:
_____________
Code for TallyGenerator
January 10, 2017 at 7:56 am
And if you would use sub queries, would that be flexible enough for you? I've added an additional column to the example posted earlier to demonstrate what I mean. I've also added examples in the table values for the requested 'Dr.' + 'MD' combination. I haven't implemented all options though. It's just a demo.
SELECT
*,
COALESCE(
Title + ' ' + First_Name + ' ' + MI + ' ' + Last_Name + ', ' + Suffix,
First_Name + ' ' + MI + ' ' + Last_Name + ', ' + Suffix,
Title + ' ' + First_Name + ' ' + MI + ' ' + Last_Name,
Title + ' ' + First_Name + ' ' + Last_Name + ', ' + Suffix,
First_Name + ' ' + Last_Name + ', ' + Suffix,
Title + ' ' + First_Name + ' ' + Last_Name,
First_Name + ' ' + Last_Name,
Title + ' ' + Last_Name
),
LTRIM( COALESCE(Title, '')
+ COALESCE( ' ' + First_Name, '')
+ COALESCE( ' ' + MI, '')
+ COALESCE( ' ' + Last_Name, '')
+ COALESCE( ', ' + Suffix, '')
),
(
select top (1) t.txt
from (
select 1, x.Title + ' ' + x.First_Name + isnull(' ' + x.MI, '') + ' ' + x.Last_Name + ', ' + x.Suffix
where x.Title is not null
and x.First_Name is not null
and x.Last_Name is not null
and x.Suffix is not null
and not (x.Suffix = 'MD' and x.Title = 'Dr.')
union all
select 2, x.Title + ' ' + x.First_Name + isnull(' ' + x.MI, '') + ' ' + x.Last_Name
where x.Title is not null
and x.First_Name is not null
and x.Last_Name is not null
and (x.Suffix is null or (x.Suffix = 'MD' and x.Title = 'Dr.'))
-- union all
-- etc...
) t (seqnr, txt)
order by seqnr
)
FROM(
VALUES ('Mr.', 'Luis', 'A', 'Cazares', 'Sr.'),
(NULL, 'Luis', 'A', 'Cazares', 'Sr.'),
(NULL, 'Luis', NULL, 'Cazares', 'Sr.'),
(NULL, 'Luis', NULL, 'Cazares', NULL),
('Mr.', 'Luis', NULL, 'Cazares','Sr.'),
('Mr.', 'Luis', 'A', 'Cazares', NULL),
('Mr.', NULL, NULL, 'Cazares', NULL),
('Mr.', 'Luis', NULL, NULL, NULL),
('Mr.', NULL, NULL, 'Cazares', 'Sr.'),
(NULL, NULL, 'A', 'Cazares', NULL),
('Dr.', 'Luis', NULL, 'Cazares','MD'),
('Dr.', 'Luis', NULL, 'Cazares','Sr.')
) x (Title, First_Name, MI, Last_Name, Suffix)
You can even construct the union clauses and execute the resulting dynamic sql using sp_executesql to get some "programmability" in your rules.
January 10, 2017 at 10:22 am
MMartin1 (1/9/2017)
You forgot the NOT.
SELECT CASE WHEN NULL IS NOT NULL THEN NULL ELSE NULL END;
How about
SELECT CASE WHEN NULL IS NOT NULL THEN NOT NULL ELSE NULL END
?
same logical result to the original expression COALESCE(null, null) . Though if you keep going these nulls can start to become a head spinner.
CASE cannot return a Boolean predicate, and since NOT is a Boolean operator, you cannot return NOT NULL in your THEN clause.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 10, 2017 at 11:51 am
drew.allen (1/10/2017)
MMartin1 (1/9/2017)
You forgot the NOT.
SELECT CASE WHEN NULL IS NOT NULL THEN NULL ELSE NULL END;
How about
SELECT CASE WHEN NULL IS NOT NULL THEN NOT NULL ELSE NULL END
?
same logical result to the original expression COALESCE(null, null) . Though if you keep going these nulls can start to become a head spinner.
CASE cannot return a Boolean predicate, and since NOT is a Boolean operator, you cannot return NOT NULL in your THEN clause.
Drew
Hence why I mentioned logically 🙂
----------------------------------------------------
January 10, 2017 at 12:09 pm
MMartin1 (1/10/2017)
drew.allen (1/10/2017)
MMartin1 (1/9/2017)
You forgot the NOT.
SELECT CASE WHEN NULL IS NOT NULL THEN NULL ELSE NULL END;
How about
SELECT CASE WHEN NULL IS NOT NULL THEN NOT NULL ELSE NULL END
?
same logical result to the original expression COALESCE(null, null) . Though if you keep going these nulls can start to become a head spinner.
CASE cannot return a Boolean predicate, and since NOT is a Boolean operator, you cannot return NOT NULL in your THEN clause.
Drew
Hence why I mentioned logically 🙂
Except it's not really logical to write code that will not work which will confuse future readers of this thread and actually wonder what they're doing wrong if they try to test the example you posted.
And on that thought, we should start a "Things to try that won't work" thread to see how many people take it seriously. Who will be posting on April 1st? @=)
January 10, 2017 at 12:16 pm
Brandie Tarvin (1/10/2017)
MMartin1 (1/10/2017)
drew.allen (1/10/2017)
MMartin1 (1/9/2017)
You forgot the NOT.
SELECT CASE WHEN NULL IS NOT NULL THEN NULL ELSE NULL END;
How about
SELECT CASE WHEN NULL IS NOT NULL THEN NOT NULL ELSE NULL END
?
same logical result to the original expression COALESCE(null, null) . Though if you keep going these nulls can start to become a head spinner.
CASE cannot return a Boolean predicate, and since NOT is a Boolean operator, you cannot return NOT NULL in your THEN clause.
Drew
Hence why I mentioned logically 🙂
Except it's not really logical to write code that will not work which will confuse future readers of this thread and actually wonder what they're doing wrong if they try to test the example you posted.
And on that thought, we should start a "Things to try that won't work" thread to see how many people take it seriously. Who will be posting on April 1st? @=)
It is not really writing code as much as using the language in a pseudo fashion to make the logical statement. Anyone trying out the syntax will quickly realise that it throws an error.
----------------------------------------------------
January 10, 2017 at 12:24 pm
MMartin1 (1/10/2017)
Brandie Tarvin (1/10/2017)
MMartin1 (1/10/2017)
drew.allen (1/10/2017)
MMartin1 (1/9/2017)
You forgot the NOT.
SELECT CASE WHEN NULL IS NOT NULL THEN NULL ELSE NULL END;
How about
SELECT CASE WHEN NULL IS NOT NULL THEN NOT NULL ELSE NULL END
?
same logical result to the original expression COALESCE(null, null) . Though if you keep going these nulls can start to become a head spinner.
CASE cannot return a Boolean predicate, and since NOT is a Boolean operator, you cannot return NOT NULL in your THEN clause.
Drew
Hence why I mentioned logically 🙂
Except it's not really logical to write code that will not work which will confuse future readers of this thread and actually wonder what they're doing wrong if they try to test the example you posted.
And on that thought, we should start a "Things to try that won't work" thread to see how many people take it seriously. Who will be posting on April 1st? @=)
It is not really writing code as much as using the language in a pseudo fashion to make the logical statement. Anyone trying out the syntax will quickly realise that it throws an error.
Yeah... I can't see that. I would buy this argument more if it had been written more in a pseudo language fashion instead of using exact CASE statement syntax.
January 10, 2017 at 12:42 pm
Hmm, which is why you have to read the context. If people are blindly pulling code they don't understand, then they are the ones choosing to take risk. If they don't err here, it will happen somewhere else. Still I take your point that is is more helpful to throw in a "don't try this at home" disclaimer.
----------------------------------------------------
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply