Search code examples
sqlsql-view

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.

Solution

  • 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.