April 20, 2017 at 2:10 pm
Hi ,
here is the statement
; with cte as (
select 1 as pk, 'xxx' as col1, 'x' as flag union all
select 1 as pk, NULL as col1, 'y' as flag union all
select 1 as pk, NULL as col1, 'z' as flag union all
select 2 as pk, NULL as col1, 'x' as flag union all
select 2 as pk, 'yyy' as col1, 'y' as flag union all
select 2 as pk, NULL as col1, 'z' as flag union all
select 3 as pk, NULL as col1, 'x' as flag union all
select 3 as pk, NULL as col1, 'y' as flag union all
select 3 as pk, 'zzz' as col1, 'z' as flag
)
select pk,
min(case when flag = 'x' then col1 else null end) as f1,
min(case when flag = 'y' then col1 else null end) as f2,
min(case when flag = 'z' then col1 else null end) as f3,
min(case when flag = 'w' then col1 else null end) as f4
from cte
group by pk
that returns
pk f1 f2 f3 f4
1 xxx NULL NULL NULL
2 NULL yyy NULL NULL
3 NULL NULL zzz NULL
My question is about F4: why does not it throw an error of "Operand data type NULL is invalid for min operator", flag in my sample is never 'w', so the last min will operate with all the NULLs only, right? lol
Thanks!
April 20, 2017 at 2:40 pm
It's using the datatype of Col1, same as it is for f1, f2 & f3.
April 20, 2017 at 2:48 pm
But col1 IS nullable, right? I am still not clear 🙁
April 20, 2017 at 2:49 pm
btio_3000 - Thursday, April 20, 2017 2:10 PMMy question is about F4: why does not it throw an error of "Operand data type NULL is invalid for min operator", flag in my sample is never 'w', so the last min will operate with all the NULLs only, right? lol
Why do you think it would throw an error? NULL will simply be ignored by a MIN or MAX function:
http://www.sqlservercentral.com/articles/Advanced+Querying/gotchasqlaggregatefunctionsandnull/1947/
so since there are no rows where Flag = 'w', the MIN() calculation will have no valid values to consider and thus return NULL as the end result.
April 20, 2017 at 3:11 pm
btio_3000 - Thursday, April 20, 2017 2:48 PMBut col1 IS nullable, right? I am still not clear 🙁
You're using a table expression (CTE), without any data definition rather than an actual table with explicitly defined data types... But for the sake of simplicity, yes col1 is clearly nullable as evidenced by the fact that NULL values are present.
But.. "nullable" isn't a data type. It's column constraint.
The MIN & MAX simply don't have a problem handling NULL values.
April 20, 2017 at 3:54 pm
totally confused here...
we cannot have MIN/MAX of all the NULLs, right? MIN/MAX does handle NULLs w/o any problems if there is at least one non-null value, but if there are all NULLS, it does throw that error
by definition , my F4 would always operates with NULLs only, but there is no error thrown
what am I missing?
Thanks!
April 21, 2017 at 1:07 am
btio_3000 - Thursday, April 20, 2017 3:54 PMtotally confused here...we cannot have MIN/MAX of all the NULLs, right? MIN/MAX does handle NULLs w/o any problems if there is at least one non-null value, but if there are all NULLS, it does throw that error
by definition , my F4 would always operates with NULLs only, but there is no error thrown
what am I missing?
Thanks!
See if this can help you understand :-
CASE Expression returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.
In this scenario, you are using col1 as result_expression in each case expression which has dataype 'varchar'. So, return type from each case if varchar not void. And data of type varchar is allowed in min() function.
Regards
VG
April 21, 2017 at 9:58 am
btio_3000 - Thursday, April 20, 2017 3:54 PMwe cannot have MIN/MAX of all the NULLs, right? MIN/MAX does handle NULLs w/o any problems if there is at least one non-null value, but if there are all NULLS, it does throw that error
you may see a warning such as "Warning: Null value is eliminated by an aggregate or other SET operation."
but there is no error and it happily continues running ignoring all the NULL values. Since it has no result to return from the aggregation, it returns NULL.
April 21, 2017 at 12:29 pm
Oh, I see...
if I do cast(NULL as varchar(10)), for example, then it does not throw that error, I see. Got it.
Thank you!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply