CODEWITHSUNDEEP
sqlsql-server
Jeremy
Jeremy

Reputation: 33

How can I use pre-used sub-queries in a where clause?

I'm putting together a long query that after much experimentation I've found I can only do through subqueries.

The subqueries are joined against a root table containing a list of financial periods. I'd like to filter the results by referencing the subqueries in the where clause without repeating the subqueries.

IE: 

Select  Year
        , Period
        , (Select.... ) as Col1
        , (Select.... ) as Col2
        , (Select.... ) as Col3
Where   Col1 > 0 or Col2 > 0 or Col3 > 0

I know I can't reference the subqueries by the given names and I've looked into referring by number but don't see any promise there. What I've ended up doing is putting the query in a stored procedure which uses it to populate a temporary table and select from that with the appropriate where clause.

Select .... into #Temp
Select * From #Temp Where Col1 > 0 or Col2 > 0 or Col3 > 0

Is there a cleaner or more efficient way to go about this?

Any thoughts?

Upvotes: 2

Views: 76

Answers (1)

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

If you're using SQL Server 2005 or greater, you can use a CTE:

;WITH Result (Year, Period, Col1, Col2, Col3) AS
(
    Select Year, Period, (Select.... ) as Col1, (Select.... ) as Col2...
)
Select *
From Result
Where Col1 > 0 or Col2 > 0 or Col3 > 0

Or, if you're using a version that doesn't support CTEs, you can treat your query as an intermediate result, and then have an outer query that applies the filtering:

Select
    *
from
(
    Select Year, Period, (Select.... ) as Col1, (Select.... ) as Col2...
) q
Where Col1 > 0 or Col2 > 0 or Col3 > 0

Upvotes: 4

Related Questions