Possible to set explicit type in CTE?-CodePudding

I can create a CTE that contains named columns of inferred type with:

WITH employees(name, salary) AS (
    SELECT 'Paul', 10000
) SELECT * FROM employees

Is there a way to specify the type in the CTE itself, for example doing:

WITH employees(name , salary DECIMAL(8,2)) AS (
    SELECT 'Paul', 10000
) SELECT * FROM employees

Or do I have to always do it within the SELECT statement, such as:

WITH employees(name, salary) AS (
    SELECT 'Paul', DECIMAL(8,2) '10000'
) SELECT * FROM employees

CodePudding user response：

You can cast the values in the CTE:

WITH employees(name, salary) AS (
  select 'Paul', cast(10000 as decimal(8,2))
) 
SELECT * 
FROM employees

Or using a VALUES clause which removes the need for a UNION if multiple rows are needed.

WITH employees(name, salary) AS (
  values 
     ('Paul', cast(10000 as decimal(8,2))), 
     ('Peter', 5000)
) 
SELECT * 
FROM employees

The cast is only needed in the first row, because that defines the data type of that column for all other rows from the VALUES clause (or a SELECT with a UNION)

CodePudding user response：

I've run you SQL in SQl Fiddle and the only way to coerce the type in the resultset I've found is below:

WITH employees(name , salary ) AS 
(
SELECT 'Paul', cast( 10000 as DECIMAL(8,2))
) 
SELECT * FROM employees