T-SQL: returning VARCHAR in a derived column

Question

I am having problems returning a VARCHAR out of a derived column.

Below are extremely simplified code examples.

I have been able to do this before:

SELECT *, message =
CASE
WHEN (status = 0)
THEN 'aaa'
END
FROM products

But when I introduce a Common Table Expression or Derived Table:

WITH CTE_products AS (SELECT * from products)
SELECT *, message =
CASE WHEN (status = 0)
THEN 'aaa'
END
FROM CTE_products

this seems to fail with the following message:

Conversion failed when converting the varchar value 'aaa' to data type int. 

When I tweak the line to say:

WITH CTE_products AS (SELECT * from products)
SELECT *, message =
CASE WHEN (status = 0)
THEN '123'
END
FROM CTE_products

It returns correctly.

...

When I remove all the other clauses prior to it, it also works fine returning 'aaa'.

My preference would be to keep this as a single, stand-alone query.

Answer 1

The problem is that the column is an integer dataype and sql server is trying to convert 'aaa' to integer

one way

WITH CTE_products AS (SELECT * from products)
SELECT *, message =
CASE WHEN (status = 0)
THEN 'aaa' else convert(varchar(50),status) 
END
FROM CTE_products

Answer 2

I actually ended up finding the answer.

One of my CASE/WHEN clauses used a derived column from the CTE and that ended up causing the confusion.

Before:

WITH CTE_products AS (SELECT *, qty_a + qty_b as qty_total FROM products)
SELECT *, message =
CASE WHEN (status = 0)
THEN 'Status is 0, the total is: ' + qty_total + '!'
END
FROM CTE_products

Corrected:

WITH CTE_products AS (SELECT *, qty_a + qty_b as qty_total FROM products)
SELECT *, message =
CASE WHEN (status = 0)
THEN 'Status is 0, the total is: ' + CAST(qty_total AS VARCHAR) + '!'
END
FROM CTE_products

I ended up removing WHEN/THEN clauses within the CASE statement right afterwards to see if it was a flukey parentheses error when I realized that in the absence of any of the WHEN/THEN clauses that included the derived column from the CTE, it was able to return VARCHAR.