CASE expressions data type compatibility

In a CASE expression, all the results should be of the same data type or be compatible data types.

If the results in all the WHEN ... THEN branch clauses are not of the same data type or compatible data types, an error occurs.

The following table shows which character data types are compatible and the data type that is returned for each combination.

Table 1. Data types returned from compatible character data types
Data type	NCHAR (>255)	NCHAR (<=255)	NVARCHAR	CHAR (<=255)	CHAR (>255)	VARCHAR	LVARCHAR (>255)	LVARCHAR (<=255)
NCHAR (>255)	NCHAR	NCHAR	NCHAR	NCHAR	NCHAR	NCHAR	NCHAR	NCHAR
NCHAR (<=255)	NCHAR	NCHAR	NVARCHAR	NCHAR	NCHAR	NVARCHAR	NCHAR	NCHAR
NVARCHAR	NCHAR	NVARCHAR	NVARCHAR	NVARCHAR	NCHAR	NVARCHAR	NCHAR	NVARCHAR
CHAR (<=255)	NCHAR	NCHAR	NVARCHAR	CHAR	CHAR	VARCHAR	CHAR	CHAR
CHAR (>255)	NCHAR	NCHAR	NCHAR	CHAR	CHAR	CHAR	CHAR	CHAR
VARCHAR	NCHAR	NVARCHAR	NVARCHAR	VARCHAR	CHAR	VARCHAR	CHAR	VARCHAR
LVARCHAR (>255)	NCHAR	NCHAR	NCHAR	CHAR	CHAR	CHAR	LVARCHAR	LVARCHAR
LVARCHAR (<=255)	NCHAR	NCHAR	NVARCHAR	CHAR	CHAR	VARCHAR	LVARCHAR	LVARCHAR

The following table shows which numeric data types are compatible and the data type that is returned for each combination.

Table 2. Data types returned from compatible numeric data types
Data type	INTEGER	SMALLINT	SERIAL	DECIMAL	FLOAT	SMALLFLOAT	MONEY	BIGINT	BIGSERIAL
INTEGER	INTEGER	INTEGER	INTEGER	DECIMAL	DECIMAL	DECIMAL	MONEY	DECIMAL	DECIMAL
SMALLINT	INTEGER	SMALLINT	INTEGER	DECIMAL	DECIMAL	DECIMAL	MONEY	DECIMAL	DECIMAL
SERIAL	INTEGER	INTEGER	SERIAL	DECIMAL	DECIMAL	DECIMAL	MONEY	DECIMAL	DECIMAL
DECIMAL	DECIMAL	DECIMAL	DECIMAL	DECIMAL	DECIMAL	DECIMAL	MONEY	DECIMAL	DECIMAL
FLOAT	DECIMAL	DECIMAL	DECIMAL	DECIMAL	FLOAT	FLOAT	MONEY	DECIMAL	DECIMAL
SMALLFLOAT	DECIMAL	DECIMAL	DECIMAL	DECIMAL	FLOAT	SMALLFLOAT	MONEY	DECIMAL	DECIMAL
MONEY	MONEY	MONEY	MONEY	MONEY	MONEY	MONEY	MONEY	MONEY	MONEY
BIGINT	DECIMAL	DECIMAL	DECIMAL	DECIMAL	DECIMAL	DECIMAL	MONEY	BIGINT	BIGINT
BIGSERIAL	DECIMAL	DECIMAL	DECIMAL	DECIMAL	DECIMAL	DECIMAL	MONEY	BIGINT	BIGSERIAL