Sql - Do you know what the Sql CTE is?

From Sql Server 2005 Bol for WITH CTE (common_table_expression):
'Specifies a temporary named result set, known as a common table expression (CTE).
 This is derived from a simple query and defined within the execution scope of
 a single SELECT, INSERT, UPDATE,
 or DELETE statement.'

Now this works fine (using the NorthWind database in Sql Server 2005):

WITH Emps (Emp, Cnt) AS
(
    SELECT employeeid,count(*) AS Cnt
    FROM orders
    GROUP BY employeeid
)
SELECT Max(Cnt)
FROM Emps

But try to define a CTE independently. You can't.
So this CTE craps out:

WITH Emps (Emp, Cnt) AS
(
    SELECT employeeid,count(*) AS Cnt
    FROM orders
    GROUP BY employeeid
)

Try to use the CTE in a batch with a 2nd SELECT. You can't 'reuse' the CTE.
So this batch craps out with an error thrown on the use of the CTE
on the 2nd SELECT.

WITH Emps (Emp, Cnt) AS
(
    SELECT employeeid,count(*) AS Cnt
    FROM orders
    GROUP BY employeeid
)
SELECT Max(Cnt)
FROM Emps

SELECT Min(Cnt)
FROM Emps       -- Invalid object name 'Emps'.

Now what if a CTE in a batch could be defined independently and
be used with multiple SELECT statements. What would the batch look
like. Well it could look something like this using the D4 language of Dataphor
(see http://beyondsql.blogspot.com/2007/09/dataphor-all-tables-are-typed-variables.html
 for some more background):

//Define a CTE independently. And to define a CTE independently it must
//be stored in a variable. We are using a pass-thru query from D4 to Sql Server
//in t-sql to the NorthWind database.
var EMPS.CTE:=
              SQLQuery("SELECT employeeid,count(*) AS Cnt
                          FROM orders
                            GROUP BY employeeid");
//Define a row using the CTE.
var LRow:=row{Min(Cnt from EMPS.CTE) MinCnt,Max(Cnt from EMPS.CTE) MaxCnt};//A row.  
//SELECT the row.
select LRow; 
/*
MinCnt MaxCnt
------ ------
42     156
*/
//Use the CTE in a 2nd SELECT, SELECT a table
select EMPS.CTE return 2 by {Cnt desc};
/*
employeeid Cnt
---------- ---
4          156
3          127
*/

What is really going on here. Now Sql has managed to immunize itself
against computer science forever. But if we were to apply a compute
science term to the sql CTE what term what we call it. We would call
it a value. Because a value cannot exist independently (by itself :))
and cannot be reused. To reuse a value we have to declare every time
we want to use it. With a variable we don't have to worry about
any of these value limitations. We can simply reuse the variable
as much as we want. The 'var' in 'var EMPS.CTE:=..' means define
a variable that contains a value and the value is the CTE SELECT
statement. So now you know the sql term 'result set' is really just
a value :) And now you know the benefit of working with variables
over values.

Of course the situation with the sql CTE is not the only case where
you have to repeat the damn thing to use it.

This works fine in sql.

SELECT orderid,customerid,employeeid
FROM orders
WHERE customerid in ('COMMI','TORTU','HUNGO')

But can you store the list of ('COMMI','TORTU','HUNGO') in a variable? No.
You have to repeat it every time you want to use it. It's because there is
no variable available to assign the list to. Of course in D4 we can
assign the list to a variable and use the variable just like the CTE.

var LList:=list(String){'COMMI','TORTU','HUNGO'};
select Orders
where CustomerID in LList
{OrderID,CustomerID,EmployeeID};

Doesn't this make more sense than having to use xml in sql to shred the
string or other crazy sql techniques to store the list items in a table?