CODEWITHSUNDEEP
sqlsql-servert-sql
Shilpi
Shilpi

Reputation: 25

Getting error while creating View in SQL server

Hi Could any one please help me while creating a view in SQL server toad I'm getting below error.Thanks in Advanced.

USE [database];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE VIEW [dbo].[GCRM_CONTRACT_ENTITLEMENT] ("objid","CONTRACT__TITLE")
AS

(with mycte as (
 SELECT  table1.Column1 as objid, 
 table1.Column2 CONTRACT__TITLE

      FROM table1       
      WHERE
      (
      (table1.struct_type = 0)                
      )     

)

SELECT  Stuff(( SELECT ',' + cast(CONTRACT__TITLE as varchar(2000)) 
           FROM mycte t2
          WHERE t2.[objid] = t1.[objid]             
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS [AVILABLE_ENTITLEMENT]
  FROM mycte t1
GROUP BY t1.[objid]
);
GO

Error:- SQL Server Database Error: Incorrect syntax near the keyword 'with'.

Upvotes: 0

Views: 197

Answers (1)

dbajtr
dbajtr

Reputation: 2044

A CTE must be the first part of the batch, docs are here.

Change the body of your view to something like this:

WITH mycte(objid,
           CONTRACT__TITLE)
     AS (
     SELECT table1.Column1 AS objid,
            table1.Column2 AS CONTRACT__TITLE
     FROM table1
     WHERE table1.struct_type = 0)
     SELECT STUFF(
                 (
                     SELECT ','+CAST(CONTRACT__TITLE AS VARCHAR(2000))
                     FROM mycte t2
                     WHERE t2.[objid] = t1.[objid] FOR XML PATH(''), TYPE
                 ).value('.', 'varchar(max)'), 1, 1, '') AS [AVILABLE_ENTITLEMENT]
     FROM mycte t1
     GROUP BY t1.[objid];

EDIT: with added INCIDENT__ID

WITH mycte(objid,
           CONTRACT__TITLE,
           INCIDENT__ID)
     AS (
     SELECT TABLE1.COLUMN1 AS objid,
            TABLE1.COLUMN2 AS CONTRACT__TITLE,
            TABLE2.COLUMN1 AS INCIDENT__ID
     FROM TABLE1
     WHERE TABLE1.struct_type = 0)
     SELECT STUFF(
                 (
                     SELECT ','+CAST(CONTRACT__TITLE AS VARCHAR(2000))
                     FROM mycte t2
                     WHERE t2.[objid] = t1.[objid] FOR XML PATH(''), TYPE
                 ).value('.', 'varchar(max)'), 1, 1, '') AS [AVILABLE_ENTITLEMENT],
            [INCIDENT__ID]
     FROM mycte t1
     GROUP BY t1.[objid],
              t1.[INCIDENT__ID];

Upvotes: 1

Related Questions