Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Specify data type with column alias in SQL Server 2008

Imagine I have the following SELECT statement in a view (SQL Server 2008):

SELECT (SELECT CASE 
                 WHEN HISTORY.OUTOFSERV = 'Y' OR HISTORY.OUTOFSERV = 'y' THEN 1 
                 ELSE 0 
               END) AS OOS 
  FROM HISTORY

The column OOS ends up being of type int, but I'd like it to be of type bit. How can I accomplish this?

like image 998
Brennan Vincent Avatar asked Jun 28 '10 20:06

Brennan Vincent


People also ask

How do I specify a column alias in SQL?

The basic syntax of a table alias is as follows. SELECT column1, column2.... FROM table_name AS alias_name WHERE [condition];

How do you create an alias type in SQL?

In the New User-defined Data Type dialog box, in the Schema box, type the schema to own this data type alias, or use the browse button to select the schema. In the Name box, type a name for the new data type alias. In the Data type box, select the data type that the new data type alias will be based on.

What is alias data type?

The DATETIME, INT2, INT4, INT8, FLOAT4, FLOAT8, and BPCHAR built-in data types correspond to the identically named Netezza data types. DATETIME is an alias for the TIMESTAMP data type. INT2 is an alias for the SMALLINT data type. INT4 is an alias for the INTEGER data type.

Can you reference an alias in SQL?

You can't reference an alias except in ORDER BY because SELECT is the second last clause that's evaluated.


1 Answers

Use CAST/CONVERT to explicitly define the data type:

SELECT CAST (CASE 
               WHEN LOWER(h.outofserv) = 'y' THEN 1 
               ELSE 0 
             END AS BIT) AS OOS 
 FROM HISTORY h
like image 191
OMG Ponies Avatar answered Sep 22 '22 23:09

OMG Ponies