SQL "WITH" Clause/Statement

Before I begin by putting a lot SQL statements to help solve my issue I might be able to get the answer by asking a simple question. I use SQL Server 2005 on a daily basis and use the "WITH" clause to perform sub-queries. I am unfortunately in a situation now where I have to use SQL Compact which does not allow the use of the "WITH" clause to perform sub queries. What is the substitute of the "WITH" clause i开发者_Python百科n SQL Compact. On average I am using 10 sub queries at a time.

As long as none of your CTE's (Common Table Expression - the formal name for the feature you are using) are recursive, remember that in the simplest form,

;WITH Q1 As 
(
   SELECT columns FROM Table1
)
SELECT columns FROM Q1

Can be roughly translated to:

SELECT columns FROM (SELECT columns FROM Table1) Q1

Note the 'Q1' on the end there. You have to give the subquery a name. The name you choose often doesn't matter, and so simple names are common here -- even just single letters. With 10 subqueries to string together, you might need to choose something more meaningful.

Create a temp table with the result of each with clause; use the temp tables instead of the with clause.