May 18, 2017 at 11:33 am
In the sql code below, I'm trying to understand why @idMin is NULL and not equal to 0. I'm probably not understanding it because I'm thinking from a C# or another programming language perspective.
Thanks for the help.
DECLARE @Client char(12) = 'NOTALL'
DECLARE @idMin int = 0
DECLARE @idMax int = 0
SELECT @idMax = MAX(idClient)
FROM client
SELECT @idMin = CASE WHEN @Client='ALL' THEN @idMax END
SELECT @idMin --the value is null and not 0. Why?
May 18, 2017 at 11:40 am
You haven't supplied a value for the ELSE condition in your CASE expression, so it is using the default NULL value. If you want it to be 0, you should add ELSE 0 to your CASE expression.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 18, 2017 at 11:42 am
If no comparison in a CASE expression evaluates to TRUE, and you don't specify an ELSE argument for the CASE, then NULL is returned.
In this case, you've only defined a comparison for @Client='ALL', and that will not evaluate to TRUE (you've set @Client='NOTALL').
Since you don't have an ELSE, and the comparison didn't evaluate to TRUE, the CASE expression returns NULL.
If you want anything other than 'ALL' for @Client to evaluate to 0, then you should do CASE WHEN @Client='ALL THEN @idMax ELSE 0 END
Cheers!
EDIT: Typing too slow. +1 to Drew 🙂
May 18, 2017 at 11:58 am
Thanks Drew and Jacob.
I don't understand why the value becomes NULL if I don't provide the else condition. I declared the variable with a default value of 0.
May 18, 2017 at 12:00 pm
rs80 - Thursday, May 18, 2017 11:58 AMThanks Drew and Jacob.I don't understand why the value becomes NULL if I don't provide the else condition. I declared the variable with a default value of 0.
As we said, the CASE expression evaluates to NULL, since none of your conditions are met.
With that assignment of the result of the CASE expression to the variable, you're basically doing SELECT @idMin=NULL.
It doesn't matter what you defined it as at the beginning, because you just assigned NULL to it.
Cheers!
May 18, 2017 at 12:21 pm
Didn't realize that's how the evaluation in SQL works.
Thanks.
May 18, 2017 at 12:39 pm
the CASE expression will always return a value, it's not like executing something after an IF condition.
May 19, 2017 at 10:43 am
If you want the default value that you declared to be considered into the Case statement then this is work around in case expression
DECLARE @Client char(12) = 'NOTALL'
DECLARE @idMin int = 0
DECLARE @idMax int = 0
SELECT @idMin = CASE WHEN @Client='ALL' THEN @idMax else @idMin END
SELECT @idMin
Thanks.
May 19, 2017 at 11:17 am
OceanDrop - Friday, May 19, 2017 10:43 AMIf you want the default value that you declared to be considered into the Case statement then this is work around in case expression
DECLARE @Client char(12) = 'NOTALL'
DECLARE @idMin int = 0
DECLARE @idMax int = 0SELECT @idMin = CASE WHEN @Client='ALL' THEN @idMax else @idMin END
SELECT @idMin
Thanks.
More like this:
DECLARE @Client char(12) = 'NOTALL';
DECLARE @idMin int = 0;
DECLARE @idMax int = 0;
SELECT @idMax = MAX(idClient)
FROM client;
IF @Client='ALL'
SELECT @idMin = @idMax ;
SELECT @idMin; --the value is null and not 0. Why?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply