CODEWITHSUNDEEP
sqlsql-servert-sql
user2657115
user2657115

Reputation: 11

SQL - WHERE on new column

I've a table with 3 columns: Id, Price, Total. I'm writing this sql statement:

SELECT Id, Price, Total,
CASE WHEN [Total] IS NULL THEN '0'
     WHEN [Total] IS NOT NULL '1' 
     ELSE ''
END AS NewColumnName
FROM Table

If I run this sql, I have no error. But if I add a Where, like this: WHERE NewColumnName= '1' the server return an error: the name of column NewColumnName is not valid.

Please help me! Thanks a lot!! RM

Upvotes: 0

Views: 285

Answers (6)

Nick H.
Nick H.

Reputation: 1616

USE [AdventureWorks2012]

WITH cte as (
SELECT [AddressID], [AddressLine1]
        , CASE WHEN [City] = 'Bothell' THEN 1 ELSE 0 END AS [NewColumn]
FROM Person.Address )

SELECT * FROM CTE
WHERE [NewColumn] = 1

So yours would be re-written like this: 

WITH cte as (
SELECT Id, Price, Total,
CASE WHEN [Total] IS NULL THEN '0'
     WHEN [Total] IS NOT NULL '1' 
     ELSE ''
END AS NewColumnName
FROM Table) 

SELECT * FROM CTE where [NewColumnName] = 1

Upvotes: 1

Jodrell
Jodrell

Reputation: 35726

Generally use a subquery,

SELECT Id, Price, Total, NewColumnName
    FROM ( 
        SELECT
                    Id,
                    Price,
                    Total,
                    CASE
                        WHEN [Total] IS NULL THEN '0'
                        ELSE '1'
                    END [NewColumnName]
            FROM
                    Table) [WithNew]
     WHERE
         NewColumnName = '1';

or in your case you could do,

SELECT
            Id,
            Price,
            Total,
            1 [NewColumnName]
    FROM
            Table
    WHERE
            Total IS NOT NULL;

Upvotes: 1

Dave Zych
Dave Zych

Reputation: 21887

You can just write your query like this:

SELECT Id, Price, Total,
FROM Table
WHERE Total IS NOT NULL

That's doing the same thing, except without the case statement.

Upvotes: 0

hashbrown
hashbrown

Reputation: 3516

Try this,

SELECT Id, Price, Total,
CASE WHEN [Total] IS NULL THEN '0'
     WHEN [Total] IS NOT NULL '1' 
END AS NewColumnName
FROM Table
WHERE CASE WHEN [Total] IS NULL THEN '0'
     WHEN [Total] IS NOT NULL '1' 
END = '1'

Upvotes: 1

Adarsh Shah
Adarsh Shah

Reputation: 6775

You can't use NewColumnName in the WHERE Clause. You will have to use the CASE statement again in WHERE Clause or do what @Lajos suggested.

Upvotes: 0

Lajos Veres
Lajos Veres

Reputation: 13725

Can you try this way?

select *
from (
  SELECT Id, Price, Total,
  CASE WHEN [Total] IS NULL THEN '0'
       WHEN [Total] IS NOT NULL '1' 
       ELSE ''
  END AS NewColumnName
  FROM Table
) x
where 
  NewColumnName= '1'

Upvotes: 2

Related Questions