CODEWITHSUNDEEP
sqlpostgresqltypeorm
sahil
sahil

Reputation: 25

How to use where clause on case statement result

CREATE TABLE case_in_where 
(
      id integer PRIMARY KEY,
      name text NOT NULL
);
  
INSERT INTO case_in_where (id, name) 
VALUES (1, 'foo'), (2, 'bar'), (3, 'baz');

I want to filter data but namevalue is working with the order by not with where condition how to fix this? you can try here http://sqlfiddle.com/#!15/1d3b4/62

SELECT 
    id, name, 
    CASE 
        WHEN name = 'foo' THEN true
        WHEN name = 'bar' THEN false
        ELSE NULL
    END AS namevalue
FROM 
    case_in_where
WHERE
    namevalue IS NOT NULL
ORDER BY
    namevalue

Upvotes: 0

Views: 1706

Answers (2)

The AG
The AG

Reputation: 690

Apart from using sub-query which you can do something like below:

Select * from (
  SELECT id, name, CASE 
  WHEN name = 'foo' THEN true
  WHEN name = 'bar' THEN false
  ELSE NULL
  END AS namevalue
FROM case_in_where ) t
where namevalue IS NOT NULL
 order by namevalue

you can also use CTE(Common Table Expression) as below.

Common Table Expression: Also called as CTE, is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. The CTE can also be used in a View.

With tmp AS (SELECT id, name, 
  CASE WHEN name = 'foo' THEN true
  WHEN name = 'bar' THEN false
  ELSE NULL END  namevalue
FROM case_in_where
 )

select * from tmp
where namevalue is not NULL
order by namevalue;

If you want to read more about CTE, you can read here: https://www.sqlshack.com/sql-server-common-table-expressions-cte/

We do not use newly created column with CASE expression as alias in the WHERE clause. For Order By, you can do the following but again you won't be able to use NULL condition in the WHERE clause:

SELECT id, name, 
  CASE WHEN name = 'foo' THEN true
  WHEN name = 'bar' THEN false
  ELSE NULL END  namevalue
FROM case_in_where
 order by 3 desc;

I hope this clarifies your confusion.

Upvotes: 1

Stefanov.sm
Stefanov.sm

Reputation: 13069

Use your query as embedded into the FROM clause and move the WHERE clause out. There are other options too, notably a CTE as @jarlh noted.

select * from
(
 SELECT id, name, CASE 
  WHEN name = 'foo' THEN true
  WHEN name = 'bar' THEN false
  ELSE NULL
  END AS namevalue
 FROM case_in_where
) t
where namevalue is not NULL
order by namevalue;

Upvotes: 1

Related Questions