Modify Return Value of SELECT-Statement (TSQL) [Optimizing query]

Question

Problem: A Database collumn has a Tristate (0,1,2). Each of the values are used serversidely.

The Clientcode (which cant be changed anymore) is only able to understand '0,1'. In the Clients view '1' is identic with '2'. So I want to change the SQL Query in the Database to return '1', if the specific value is > 0.

My current Solution is combining 2 Selects (using UNION SELECT) with different WHERE-Clauses and returning '1' or '0' as static values. Now I'm looking for a solution to 'translate' the value within only ONE SELECT statement.

This is my current Solution:

 SELECT
dbo.Nachricht.NachrichtID, dbo.Nachricht.Bezeichnung, '1' AS BetrifftKontoeinrichtung, 
FROM         dbo.Nachricht INNER JOIN dbo.AdditionalData
ON dbo.Nachricht.NachrichtID = dbo.AdditionalData.NachrichtID

WHERE     (dbo.Nachricht.NachrichtID in ( 450,439 ))
AND dbo.AdditionalData.BetrifftKontoeinrichtung > 0

UNION SELECT
dbo.Nachricht.NachrichtID, dbo.Nachricht.Bezeichnung, '0' AS BetrifftKontoeinrichtung, 
FROM         dbo.Nachricht INNER JOIN dbo.AdditionalData
ON dbo.Nachricht.NachrichtID = dbo.AdditionalData.NachrichtID

WHERE     (dbo.Nachricht.NachrichtID in ( 450,439 ))
AND dbo.AdditionalData.BetrifftKontoeinrichtung = 0

Answer 1

You can use a case statement, like this:

SELECT
   dbo.Nachricht.NachrichtID, dbo.Nachricht.Bezeichnung, 
   CASE WHEN dbo.AdditionalData.BetrifftKontoeinrichtung = 0 
        THEN '0' ELSE '1' 
   END AS BetrifftKontoeinrichtung, 
FROM dbo.Nachricht
INNER JOIN dbo.AdditionalData
        ON dbo.Nachricht.NachrichtID = dbo.AdditionalData.NachrichtID    
WHERE (dbo.Nachricht.NachrichtID in ( 450,439 ))

Answer 2

Looks like you need to use CASE. A decent tutorial here http://www.databasejournal.com/features/mssql/article.php/3288921/T-SQL-Programming-Part-5---Using-the-CASE-Function.htm

See the worked example

Answer 3

If you just CAST(CAST(val AS BIT) AS INT) you will get integer 0 for 0 and integer 1 for everything else.