Select From CTE using AS? SQL Server 2008

Question

I'm trying to convert a PostgreSQL into SQL Server. But this query doesn't work.

What am I doing wrong? I tried to add a semicolon before WITH but no luck.

   SELECT 
      member_a AS you, member_b AS mightknow, shared_connection,
      CASE
         WHEN (n1.member_job_country = n2.member_job_country AND n1.member_job_country = n3.member_job_country) THEN 'country in common'
         WHEN (n1.member_unvan_id = n2.member_unvan_id AND n1.member_unvan_id = n3.member_unvan_id) THEN 'unvan in common'
         ELSE 'nothing in common'
      END AS reason
   FROM (
      WITH transitive_closure(member_a, member_b, distance, path_string, direct_connection) AS
        (SELECT 
             member_a, member_b, 1 AS distance,
             CAST(member_a as varchar(MAX)) + '.' + CAST(member_b as varchar(MAX)) + '.' AS path_string,
             member_b AS direct_connection
         FROM Member_Contact_Edges
         WHERE member_a = 45046 -- set the starting node

         UNION ALL

         SELECT 
             tc.member_a, e.member_b, tc.distance + 1,
             CAST(tc.path_string as varchar(MAX)) + CAST(e.member_b as varchar(MAX)) + '.' AS path_string,
             tc.direct_connection
         FROM Member_Contact_Edges AS e
         JOIN transitive_closure AS tc ON e.member_a = tc.member_b
         WHERE tc.path_string NOT LIKE '%' + CAST(e.member_b as varchar(MAX)) + '.%'
         AND tc.distance < 2
        )

   SELECT
       member_a, member_b,direct_connection AS shared_connection
   FROM transitive_closure
   WHERE distance = 2
  ) AS youmightknow
  LEFT JOIN Members AS n1 ON youmightknow.member_a = n1.memberID
  LEFT JOIN Members AS n2 ON youmightknow.member_b = n2.memberID
  LEFT JOIN Members AS n3 ON youmightknow.shared_connection = n3.memberID
  WHERE (n1.member_job_country = n2.member_job_country 
         AND n1.member_job_country = n3.member_job_country)
        OR (n1.member_unvan_id = n2.member_unvan_id 
            AND n1.member_unvan_id = n3.member_unvan_id);

Error I get:

Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 34
Incorrect syntax near ')'.

Here is the reference; Graphs in the Database - SQL Meets Social Networks - Look at the facebook suggestion part at the bottom of the article.

Thanks in advance

Solution 1

The CTE declaration needs to go at the top. You can also have multiple CTEs declared and joined by commas rather than mixing CTEs and derived tables.

Try

;WITH 
/*First CTE declaration*/
transitive_closure(member_a, member_b, distance, path_string, direct_connection) 
AS
(   
     ...
),
/*Second CTE declaration*/
youmightknow AS
(
SELECT member_a, member_b,direct_connection AS shared_connection
FROM transitive_closure
WHERE distance = 2
)        
SELECT member_a AS you,
...
FROM youmightknow

Solution 2

Move your CTE definition to the beginning of your SQL statement. The keyword WITH appears once at the beginning of your statement to introduce one or more CTEs, which may then be referred to in the following SQL. Take a look at this CTE example, it will clear it up for you.

Comments

[Burak F. Kilicaslan]

thank you @Martin working like a charm now and @Michael Ludwig: thanks for the reference it helped me a lot.