April 12, 2010 at 1:41 pm
This seems like it should be simple, but I am running into a problem with it.
I have 6 columns, they will either contain an integer or decimal value, or be NULL.
I want to return 0 if they are NULL and 1 if they are NOT NULL.
I could use a CASE statement to do this, but it seems like there should be a better way.
ISNULL would work perfectly for all the NULL values, I could just return 0.
However, I need a "ISNOTNULL" or something for the flip side of that to return 1.
Thoughts, or is CASE the only way to get this done?
Thanks!
April 12, 2010 at 1:46 pm
Something like this:
create table #TestCols (
Col1 int null,
Col2 int null,
Col3 int null,
Col4 int null,
Col5 int null,
Col6 int null
);
insert into #TestCols
select null, null, null, null, null, null union all
select null, null, 1, null, null, null;
select
case when coalesce(Col1,Col2,Col3,Col4,Col5,Col6) is null then 0 else 1 end as NullCols
from
#TestCols;
Or is there more to this than I have read?
April 12, 2010 at 2:07 pm
April 12, 2010 at 2:10 pm
bteraberry (4/12/2010)
If you have an aversion for case statements you could also do
select isnull(CAST(ceiling(coalesce(Col1,Col2,Col3,Col4,Col5,Col6)) as bit), 0)😛
I'm pretty sure, if one of the values is 0, that will return a 0. 0 isn't null, so it should return a 1 at that point.
Will also do that if the first value is 0, even if the others are all filled in.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 12, 2010 at 2:13 pm
GSquared (4/12/2010)
bteraberry (4/12/2010)
If you have an aversion for case statements you could also do
select isnull(CAST(ceiling(coalesce(Col1,Col2,Col3,Col4,Col5,Col6)) as bit), 0)😛
I'm pretty sure, if one of the values is 0, that will return a 0. 0 isn't null, so it should return a 1 at that point.
Good catch, thank you. I meant to point that out and I forgot .... I was distracted by the emoticon selection!
April 12, 2010 at 2:20 pm
bteraberry (4/12/2010)
GSquared (4/12/2010)
bteraberry (4/12/2010)
If you have an aversion for case statements you could also do
select isnull(CAST(ceiling(coalesce(Col1,Col2,Col3,Col4,Col5,Col6)) as bit), 0)😛
I'm pretty sure, if one of the values is 0, that will return a 0. 0 isn't null, so it should return a 1 at that point.
Good catch, thank you. I meant to point that out and I forgot .... I was distracted by the emoticon selection!
made a slight change to bteraberry's code and added it to my select statement:
select
isnull(CAST(abs(ceiling(coalesce(Col1,Col2,Col3,Col4,Col5,Col6))) + 1 as bit), 0),
case when coalesce(Col1,Col2,Col3,Col4,Col5,Col6) is null then 0 else 1 end as NullCols
from
#TestCols;
April 12, 2010 at 2:29 pm
And actually, using the +1 to correct the flaw GSquared pointed out negates the need to use the CEILING function since any non-null is going to be a number >= 1.
Still probably easier just to use the code Lynn originally posted, but it's fun nonetheless.
April 12, 2010 at 2:35 pm
bteraberry (4/12/2010)
And actually, using the +1 to correct the flaw GSquared pointed out negates the need to use the CEILING function since any non-null is going to be a number >= 1.Still probably easier just to use the code Lynn originally posted, but it's fun nonetheless.
You're right. I could have replaced the ceiling function with ABS. Guess that is what happens when juggling multiple things at once.
Now, where did I put the RoboCopy documentation....
April 12, 2010 at 3:21 pm
What I basically want to do is add up all 6 columns.
So NULL = 0 and NOT NULL = 1, then at the end I will add all 6 columns together and sort by lowest value.
Then I will know that some records are missing values for those 6 columns and I can do "stuff" about it based off if they are less than 6, but more than 0 (as leaving all NULL is a valid option per the business rules).
Didn't get a chance to read the other responses yet, will do that later tonight and comment.
April 12, 2010 at 3:36 pm
Maxer (4/12/2010)
What I basically want to do is add up all 6 columns.So NULL = 0 and NOT NULL = 1, then at the end I will add all 6 columns together and sort by lowest value.
Then I will know that some records are missing values for those 6 columns and I can do "stuff" about it based off if they are less than 6, but more than 0 (as leaving all NULL is a valid option per the business rules).
Didn't get a chance to read the other responses yet, will do that later tonight and comment.
Not what you initially asked. How about providing us with DDL for the table(s), sample data, and expected results. Be sure to use the way I provided you with code as a model for providing the same to us.
April 12, 2010 at 4:00 pm
declare @TestCols table(
RecNum int not null,
Col1 decimal(10,5) null,
Col2 decimal(10,5) null,
Col3 decimal(10,5) null,
Col4 decimal(10,5) null,
Col5 decimal(10,5) null,
Col6 decimal(10,5) null
);
insert into @TestCols
select 1, null, 5, null, 0.2, null, 8 union all
select 2, null, null, 0, null, null, null union all
select 3, null, null, -0.00001, null, 2, null union all
select 4, null, null, null, null, null, null;
select RecNum,
ISNULL(CAST(CAST(ABS(Col1)+ 1 AS BIT) as INT), 0) +
ISNULL(CAST(CAST(ABS(Col2)+ 1 AS BIT) as INT), 0) +
ISNULL(CAST(CAST(ABS(Col3)+ 1 AS BIT) as INT), 0) +
ISNULL(CAST(CAST(ABS(Col4)+ 1 AS BIT) as INT), 0) +
ISNULL(CAST(CAST(ABS(Col5)+ 1 AS BIT) as INT), 0) +
ISNULL(CAST(CAST(ABS(Col6)+ 1 AS BIT) as INT), 0) RetVal
from @TestCols
order by RetVal ASC;
April 12, 2010 at 4:01 pm
Using UnPIVOT should get you most of the way there:
(Tossing in an ID oclumn so that you know which row you're dealing with need to be adjusted)
select id,count(cols)
from
(select id, col1,col2,col3,col4,col5,col6
from #TestCols ) p
unpivot
(fun for cols in (col1,col2,col3,col4,col5,col6)) as unp
group by id
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply