CODEWITHSUNDEEP
sqlsql-serverwith-statementwith-clause
mondayguy
mondayguy

Reputation: 991

Several usage of sql with alias

I want to use several time alias of with clause on SQL server.

Example:

WITH name1 AS (
     SELECT ...
)

SELECT * from name1
SELECT * from name1 ORDER BY name

Is it possible? I'm getting "Invalid object name " error

Upvotes: 0

Views: 91

Answers (2)

Mansoor
Mansoor

Reputation: 4192

Common Table Expressions as below syntax :

WITH name1 AS 
(
  SELECT ...
)

SELECT * from name1

You can use only one SELECT statement for CTE table .If you want to use CTE table more than once,Just you move to that CTE table to temp table and use that table at all place.

Upvotes: 0

Raj
Raj

Reputation: 10853

What you are trying to use is a CTE, which is available for use only in the immediately following DML

WITH name1 AS (
     SELECT ...
)

SELECT * from name1

That part will work fine. The next select statement will not have access to the CTE. You can try using a table variable instead

Upvotes: 1

Related Questions