What is the most concise way to return CHAR(1) indicator Y/N if one of n columns in a row contain a non-null value?
Performance is important, but not the primary consideration, in this case.
The straightforward way seems to be:
SELECT CASE WHEN (C.TerminatedDate IS NULL
AND C.SelfClosedDate IS NULL
AND ...)
THEN 'Y'
ELSE 'N' END AS 'OpenInd'
FROM Customers C
Curious if there is a better way; aware of COALESCE():
SELECT CASE WHEN COALESCE (C.TerminatedDate, C.SelfClosedDate, ...) IS NULL
THEN 'Y'
ELSE 'N' END AS 'OpenInd'
FROM Customers C
Is there a better way?
Database server is SQL Server 2008.
SQL COALESCE – a function that returns the first defined, i.e. non-NULL value from its argument list. Usually one or more COALESCE function arguments is the column of the table the query is addressed to. Often a subquery is also an argument for a function.
With SQL, how can you return the number of not null records in the “Persons” table? Explanation: COUNT(column_name) is used to count the number of rows of a table where column name is a column that does not allow NULL values.
Since no one is suggesting it, and the question asks for concise
..
For the same data types, a straight COALESCE is best
coalesce(a,b,c,d) is not null
If you will be dealing with different data types, try a modified COALESCE
coalesce(LEFT(a,1),LEFT(b,1),LEFT(c,1)) is not null
Sample:
create table abc (a int, b datetime, c varchar(max), d image)
insert into abc select 1, GETDATE(), '', null
insert into abc select 1, null, '', null
insert into abc select 1, null, '', 0x123123
insert into abc select null, null, '', 0x123123
insert into abc select null, GETDATE(), '', 0x123123
insert into abc select null, null, null, null
insert into abc select 88, GETDATE()+3, null, null
insert into abc select 88, GETDATE()+3, 'gdasdf', null
insert into abc select null, null, '222', 0x123123
insert into abc select null, null, 'abcdef', 0x123123
select *, case when coalesce(LEFT(a,1),LEFT(b,1),LEFT(c,1)) is not null then 'N' else 'Y' end
from abc
If you are not using exotic types like VARCHAR(MAX) or IMAGE, then you can use SQL_VARIANT with COALESCE
create table abc (a int, b datetime, c varchar(10), d image)
insert into abc select 1, GETDATE(), '', null
insert into abc select 1, null, '', null
insert into abc select 1, null, '', 0x123123
insert into abc select null, null, '', 0x123123
insert into abc select null, GETDATE(), '', 0x123123
insert into abc select null, null, null, null
insert into abc select 88, GETDATE()+3, null, null
insert into abc select 88, GETDATE()+3, 'gdasdf', null
insert into abc select null, null, '222', 0x123123
insert into abc select null, null, 'abcdef', 0x123123
select *, case when coalesce(convert(sql_variant,a),b,c) is not null then 'N' else 'Y' end
from abc
Theoretically, you should get the advantages of short-circuiting here by immediately returning when any value is not null
Select Case
When C.TerminatedDate Is Not Null Then 'N'
When C.SelfClosedDate Is Not Null Then 'N'
...
Else 'Y'
End
That said, I doubt there is a significant difference in performance between this solution and your above solutions (assuming you can use Coalesce).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With