Computing SQL nulls

[Guest]

Hello,

I have a table

CREATE TABLE [dbo].[RequestedServices] (
[ServiceID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FollowUpID] [int] NULL
) ON [PRIMARY]

and a query

Select Name, FollowUpID from RequestedServices

but I would like to add a boolean computed column to the query called "Completed" which is true if FollowUpID is not null and false if FollowUpID is null. How would I do this? I attempted

Select Name, FollowUpID, (FollowUpID is null) AS Completed from RequestedServices

but it didn't work. I got it working by setting up a User Defined Function

Select Name, FollowUpID, dbo.IsColumnNull(FollowUpID) AS Completed from RequestedServices

but it would be much easier if I could include the logic in the Select statement without resorting to outside functions. Thanks for your help!

 

[Darren Clark]

Select Name, FollowUpID,
-- return 1 for true and 0 for false.
case isnull(FollowUpID,'a' )
when 'a' then 0
else 1
end as Completed

from RequestedServices

Is this what you want?

Hello,

I have a table

CREATE TABLE [dbo].[RequestedServices] (
[ServiceID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FollowUpID] [int] NULL
) ON [PRIMARY]

and a query

Select Name, FollowUpID from RequestedServices

but I would like to add a boolean computed column to the query called

"Completed" which is true if FollowUpID is not null and false if FollowUpID
is null. How would I do this? I attempted

Select Name, FollowUpID, (FollowUpID is null) AS Completed from RequestedServices

but it didn't work. I got it working by setting up a User Defined Function

Select Name, FollowUpID, dbo.IsColumnNull(FollowUpID) AS Completed from RequestedServices

but it would be much easier if I could include the logic in the Select

statement without resorting to outside functions. Thanks for your help!

 

[Steve C. Orr [MVP, MCSD]]

You're more likely to get responses for this kind of question in a SQL
newsgroup, but I believe the solution would be this:

Select Name, FollowUpID, IsNull(FollowUpID,0,1) AS Completed from
RequestedServices

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net

Hello,

I have a table

CREATE TABLE [dbo].[RequestedServices] (
[ServiceID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FollowUpID] [int] NULL
) ON [PRIMARY]

and a query

Select Name, FollowUpID from RequestedServices

but I would like to add a boolean computed column to the query called

"Completed" which is true if FollowUpID is not null and false if FollowUpID
is null. How would I do this? I attempted

Select Name, FollowUpID, (FollowUpID is null) AS Completed from RequestedServices

but it didn't work. I got it working by setting up a User Defined Function

Select Name, FollowUpID, dbo.IsColumnNull(FollowUpID) AS Completed from RequestedServices

but it would be much easier if I could include the logic in the Select

statement without resorting to outside functions. Thanks for your help!

 

[Steve C. Orr [MVP, MCSD]]

Correction, this syntax is more accurate:
Select Name, FollowUpID, IsNull(FollowUpID,0) AS Completed from
RequestedServices

This will return a zero if it's null, otherwise it will return the
FollowUpID.

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net

You're more likely to get responses for this kind of question in a SQL
newsgroup, but I believe the solution would be this:

Select Name, FollowUpID, IsNull(FollowUpID,0,1) AS Completed from
RequestedServices

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net

Hello,

I have a table

CREATE TABLE [dbo].[RequestedServices] (
[ServiceID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FollowUpID] [int] NULL
) ON [PRIMARY]

and a query

Select Name, FollowUpID from RequestedServices

but I would like to add a boolean computed column to the query called

"Completed" which is true if FollowUpID is not null and false if FollowUpID
is null. How would I do this? I attempted

Select Name, FollowUpID, (FollowUpID is null) AS Completed from RequestedServices

but it didn't work. I got it working by setting up a User Defined Function

Select Name, FollowUpID, dbo.IsColumnNull(FollowUpID) AS Completed from RequestedServices

but it would be much easier if I could include the logic in the Select

statement without resorting to outside functions. Thanks for your help!

 

[Guest]

Darren,

This is exactly what I was looking for! Thank you so much! It's perfect.

Select Name, FollowUpID,
-- return 1 for true and 0 for false.
case isnull(FollowUpID,'a' )
when 'a' then 0
else 1
end as Completed

from RequestedServices

Is this what you want?

Hello,

I have a table

CREATE TABLE [dbo].[RequestedServices] (
[ServiceID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FollowUpID] [int] NULL
) ON [PRIMARY]

and a query

Select Name, FollowUpID from RequestedServices

but I would like to add a boolean computed column to the query called

"Completed" which is true if FollowUpID is not null and false if FollowUpID
is null. How would I do this? I attempted

Select Name, FollowUpID, (FollowUpID is null) AS Completed from RequestedServices

but it didn't work. I got it working by setting up a User Defined Function

Select Name, FollowUpID, dbo.IsColumnNull(FollowUpID) AS Completed from RequestedServices

but it would be much easier if I could include the logic in the Select

statement without resorting to outside functions. Thanks for your help!

 

[Guest]

Darren,

This is exactly what I was looking for! Thank you so much! It's perfect.

Select Name, FollowUpID,
-- return 1 for true and 0 for false.
case isnull(FollowUpID,'a' )
when 'a' then 0
else 1
end as Completed

from RequestedServices

Is this what you want?

Hello,

I have a table

CREATE TABLE [dbo].[RequestedServices] (
[ServiceID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FollowUpID] [int] NULL
) ON [PRIMARY]

and a query

Select Name, FollowUpID from RequestedServices

but I would like to add a boolean computed column to the query called

"Completed" which is true if FollowUpID is not null and false if FollowUpID
is null. How would I do this? I attempted

Select Name, FollowUpID, (FollowUpID is null) AS Completed from RequestedServices

but it didn't work. I got it working by setting up a User Defined Function

Select Name, FollowUpID, dbo.IsColumnNull(FollowUpID) AS Completed from RequestedServices

but it would be much easier if I could include the logic in the Select

statement without resorting to outside functions. Thanks for your help!

 

[Guest]

Steve,

Darren's reply actually uses this method in combination with Case to achieve a boolean column.

Correction, this syntax is more accurate:
Select Name, FollowUpID, IsNull(FollowUpID,0) AS Completed from
RequestedServices

This will return a zero if it's null, otherwise it will return the
FollowUpID.

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net

You're more likely to get responses for this kind of question in a SQL
newsgroup, but I believe the solution would be this:

Select Name, FollowUpID, IsNull(FollowUpID,0,1) AS Completed from
RequestedServices

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net

Hello,

I have a table

CREATE TABLE [dbo].[RequestedServices] (
[ServiceID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FollowUpID] [int] NULL
) ON [PRIMARY]

and a query

Select Name, FollowUpID from RequestedServices

but I would like to add a boolean computed column to the query called

"Completed" which is true if FollowUpID is not null and false if FollowUpID
is null. How would I do this? I attempted

Select Name, FollowUpID, (FollowUpID is null) AS Completed from RequestedServices

but it didn't work. I got it working by setting up a User Defined Function

Select Name, FollowUpID, dbo.IsColumnNull(FollowUpID) AS Completed from RequestedServices

but it would be much easier if I could include the logic in the Select

statement without resorting to outside functions. Thanks for your help!

 

[Guest]

Darren,

This is exactly what I was looking for! Thank you so much! It's perfect.

Select Name, FollowUpID,
-- return 1 for true and 0 for false.
case isnull(FollowUpID,'a' )
when 'a' then 0
else 1
end as Completed

from RequestedServices

Is this what you want?

Hello,

I have a table

CREATE TABLE [dbo].[RequestedServices] (
[ServiceID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FollowUpID] [int] NULL
) ON [PRIMARY]

and a query

Select Name, FollowUpID from RequestedServices

but I would like to add a boolean computed column to the query called

"Completed" which is true if FollowUpID is not null and false if FollowUpID
is null. How would I do this? I attempted

Select Name, FollowUpID, (FollowUpID is null) AS Completed from RequestedServices

but it didn't work. I got it working by setting up a User Defined Function

Select Name, FollowUpID, dbo.IsColumnNull(FollowUpID) AS Completed from RequestedServices

but it would be much easier if I could include the logic in the Select

statement without resorting to outside functions. Thanks for your help!