Choose from CTE using AS? SQL Server 2008

Question

I am trying to convert PostgreSQL to SQL Server. But this request does not work.

What am I doing wrong? I tried adding a semicolon to 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:

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

Here's a link; Graphs in a database - SQL meets social networks - Look at the facebook sentence portion at the bottom of the article.

Thanks in advance

Answer 1

The CTE declaration should go up. You can also use multiple CTEs, declared and combined by commas, rather than mixing CTEs and views.

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 

Answer 2

Move the CTE definition to the top of your SQL statement. The WITH keyword appears once at the beginning of your statement to enter one or more CTEs, which can then be mentioned in the following SQL. Take a look at this CTE example , it will clean it for you.