CODEWITHSUNDEEP
sqlsql-serverboolean
Glowie
Glowie

Reputation: 2309

Output value and whether it exists in SQL Server 2008 Database

I am trying to create a query that outputs (1) The value (2) Whether the value exists or not. I tried 

SELECT CASE WHEN EXISTS (
    SELECT [IP_ADDR1_TEXT]
    FROM [dbo].[V_SEM_COMPUTER]
    WHERE [IP_ADDR1_TEXT] = '10.10.10.10'
) , [IP_ADDR1_TEXT] 
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END

And I get error. Thanks.

CLARIFICATION

When I try 

SELECT CASE WHEN EXISTS (
    SELECT [IP_ADDR1_TEXT]
    FROM [dbo].[V_SEM_COMPUTER]
    WHERE [IP_ADDR1_TEXT] = '10.10.10.10'
) 
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END

It only outputs 1 or 0. I would like it to output the IP address, and 1 or 0, depending on whether it exists or not.

IP Address   EXISTS
10.10.10.10    1

Upvotes: 0

Views: 58

Answers (2)

Rahul
Rahul

Reputation: 77846

This should do the work

SELECT 
CASE WHEN COUNT(distinct [IP_ADDR1_TEXT]) > 0 THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END AS Some_blah
    FROM [dbo].[V_SEM_COMPUTER]
    WHERE [IP_ADDR1_TEXT] = '10.10.10.10'

What you have already tried, will work as well. Just mention the column before CASE statement like below

SELECT [IP_ADDR1_TEXT],
    CASE WHEN EXISTS (
    SELECT [IP_ADDR1_TEXT]
    FROM [dbo].[V_SEM_COMPUTER]
    WHERE [IP_ADDR1_TEXT] = '10.10.10.10'
) 
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END
FROM [dbo].[V_SEM_COMPUTER]
WHERE [IP_ADDR1_TEXT] = '10.10.10.10'

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33571

Here is a simple way to do this. 

SELECT cast(count([IP_ADDR1_TEXT]) as bit) as IP_Exists
FROM [dbo].[V_SEM_COMPUTER]
WHERE [IP_ADDR1_TEXT] = '10.10.10.10'

Upvotes: 0

Related Questions