Need advice with ISNULL
in [Programming SQL]
|
From: Sam on 21 Apr 2010 10:56 Hi, ISNULL allows me to either return the data in a field or some specified value if the field is NULL. What I want to do is slightly different. I want to return 1 or a 0 based on whether or not there's data in the field. In other words, I don't want to return the data but a value of 1 if there is data. If there's no data, I want to return a 0. How do I do this? -- Thanks, Sam
From: Uri Dimant on 21 Apr 2010 11:01 Sam CASE Expression? SELECT CASE WHEN col IS NULL THEN 0 ELSE 1 END FROM tbl "Sam" <Sam(a)discussions.microsoft.com> wrote in message news:26D56108-CE7E-4915-8273-063BB7F65797(a)microsoft.com... > Hi, > > ISNULL allows me to either return the data in a field or some specified > value if the field is NULL. > > What I want to do is slightly different. I want to return 1 or a 0 based > on > whether or not there's data in the field. In other words, I don't want to > return the data but a value of 1 if there is data. If there's no data, I > want > to return a 0. > > How do I do this? > -- > Thanks, > > Sam
From: Dooza on 21 Apr 2010 11:01 On 21/04/2010 15:56, Sam wrote: > Hi, > > ISNULL allows me to either return the data in a field or some specified > value if the field is NULL. > > What I want to do is slightly different. I want to return 1 or a 0 based on > whether or not there's data in the field. In other words, I don't want to > return the data but a value of 1 if there is data. If there's no data, I want > to return a 0. > > How do I do this? SELECT CASE yourField WHEN IS NULL THEN 0 ELSE 1 END Dooza
From: Dan on 21 Apr 2010 11:01 "Sam" <Sam(a)discussions.microsoft.com> wrote in message news:26D56108-CE7E-4915-8273-063BB7F65797(a)microsoft.com... > Hi, > > ISNULL allows me to either return the data in a field or some specified > value if the field is NULL. > > What I want to do is slightly different. I want to return 1 or a 0 based > on > whether or not there's data in the field. In other words, I don't want to > return the data but a value of 1 if there is data. If there's no data, I > want > to return a 0. > > How do I do this? If by "no data" you mean NULL, then you could use CASE MyCol IS NULL THEN 0 ELSE 1 END -- Dan
From: Sam on 21 Apr 2010 11:05
Thanks Dan. I didn't think about using CASE. Thanks again. -- Thanks, Sam "Dan" wrote: > > "Sam" <Sam(a)discussions.microsoft.com> wrote in message > news:26D56108-CE7E-4915-8273-063BB7F65797(a)microsoft.com... > > Hi, > > > > ISNULL allows me to either return the data in a field or some specified > > value if the field is NULL. > > > > What I want to do is slightly different. I want to return 1 or a 0 based > > on > > whether or not there's data in the field. In other words, I don't want to > > return the data but a value of 1 if there is data. If there's no data, I > > want > > to return a 0. > > > > How do I do this? > > If by "no data" you mean NULL, then you could use > > > CASE MyCol IS NULL THEN 0 ELSE 1 END > > -- > Dan > > |