CODEWITHSUNDEEP
sql-server-2005
Anthony
Anthony

Reputation: 31

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 in SQL Compact. On average I am using 10 sub queries at a time.

Upvotes: 3

Views: 2539

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415735

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.

Upvotes: 7

antlersoft
antlersoft

Reputation: 14786

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

Upvotes: 2

Related Questions