CODEWITHSUNDEEP
sqlsql-view
jason
jason

Reputation: 7164

How to use View elements inside Where clause in SQL

I have this element in my database view : 

  CASE
    WHEN Type = 'Reserved' THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
  END AS isReserved,

I want to use isReserved column inside my WHERE clause. How can I do that? Thanks.

Upvotes: 1

Views: 155

Answers (3)

Martin Smith
Martin Smith

Reputation: 453047

You can use CROSS APPLY ... VALUES (in SQL Server - didn't notice it wasn't tagged for any specific DBMS)

SELECT isReserved
FROM   YourTable
       CROSS APPLY (VALUES(CASE WHEN Type = 'Reserved' THEN CAST(1 AS BIT)
                            ELSE CAST(0 AS BIT)
                           END)) CA(isReserved)
WHERE  isReserved = 1

But you're probably better off just using Type = 'Reserved' etc. in your WHERE clause directly rather than referencing the CASE result anyway. That way it is sargable and can use an index.

Upvotes: 3

Kamil Ibadov
Kamil Ibadov

Reputation: 710

You can use same expression in where clause, and oracle sql will not calculate it again, it will get result from cache

select CASE
    WHEN Type = 'Reserved' THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
  END AS isReserved
 from tablename
 where CASE
    WHEN Type = 'Reserved' THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
  END=CAST(0 AS bit)

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

You can't do it directly.

Either you include a subquery.

SELECT *
FROM (SELECT <something> as newAlias FROM YourTable) as T
WHERE T.newAlias = ....

or just repeat the definition

SELECT *
FROM YourTable
WHERE <something> = ....

Upvotes: 1

Related Questions