CODEWITHSUNDEEP
sqlsql-serverviewsyntax
John Joseph
John Joseph

Reputation: 207

CREATE VIEW syntax for WITH AS (SELECT) statements

SQL Server: I have the following working SQL statement:

WITH required_skills AS
(
    SELECT DISTINCT 
        e.empl_ID, e.job_code, j.skill_ID, j.skill_desc
    FROM 
        emplskills e
    JOIN 
        jobskills j ON e.job_code = j.job_code
)
SELECT 
    r.empl_ID, e.emplName, r.job_code, r.skill_ID, r.skill_desc
FROM
    required_skills r
FULL OUTER JOIN 
    emplskills e ON r.empl_ID = e.empl_ID
                 AND r.skill_ID = e.skill_ID;

I would like to convert the output of this statement to a view. I thought I could just do this:

CREATE VIEW MY_VIEW 
AS
    (WITH required_skills AS
     (
         SELECT DISTINCT 
             e.empl_ID, e.job_code, j.skill_ID, j.skill_desc
         FROM 
             emplskills e
         JOIN 
             jobskills j ON e.job_code = j.job_code
     )
     SELECT 
         r.empl_ID, e.emplName, r.job_code, r.skill_ID, r.skill_desc
     FROM 
         required_skills r
     FULL OUTER JOIN 
         emplskills e ON r.empl_ID = e.empl_ID AND r.skill_ID = e.skill_ID
);

But it errors out with this :

Incorrect syntax near 'WITH'

Can someone help please?

TIA, John

Upvotes: 3

Views: 4136

Answers (1)

pmbAustin
pmbAustin

Reputation: 3970

Remove the parenthesis after AS.

CREATE VIEW MyViewName
AS
WITH required_skills as (
      SELECT DISTINCT e.empl_ID, e.job_code, j.skill_ID, j.skill_desc
     FROM emplskills e
     JOIN jobskills j
       ON e.job_code = j.job_code
)
SELECT r.empl_ID, e.emplName, r.job_code, r.skill_ID, r.skill_desc
  FROM required_skills r
       FULL OUTER JOIN emplskills e
       ON r.empl_ID = e.empl_ID
      AND r.skill_ID = e.skill_ID

Upvotes: 7

Related Questions