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?
The basic syntax of a table alias is as follows. SELECT column1, column2.... FROM table_name AS alias_name WHERE [condition];
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.
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.
You can't reference an alias except in ORDER BY because SELECT is the second last clause that's evaluated.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With