Getting error while creating View in SQL server

Question

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'.

First line CREATE VIEW [dbo].[GCRM_CONTRACT_ENTITLEMENT] ("objid","CONTRACT__TITLE") in a view this is not allowed ("objid","CONTRACT__TITLE") — Pரதீப்
– Pரதீப், Commented Oct 5, 2017 at 5:41
I have created a view with parameters but first time I'm creating with "cte".so parameter not seems to b a problm. — Shilpi
– Shilpi, Commented Oct 5, 2017 at 5:46

marc_s · Accepted Answer · 2020-03-03 21:41:04Z

1

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];

edited Mar 3, 2020 at 21:41

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Oct 5, 2017 at 6:27

dbajtr

2,0442 gold badges16 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shilpi Over a year ago

Thanks thats really worked but i want one more coulmns in that view as i have only one right now "AVILABLE_ENTITLEMENT" so when i tried to add more coulms getting error like SQL Server Database Error: Column 'mycte.INCIDENT__ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

dbajtr Over a year ago

If you are adding another column in the select that isn't aggregated e.g. not one of max, min, count etc then you will also need to add the column name to your group by. If you are aggregating inside your cte you will need to add a reference to 'mycte.INCIDENT__ID' in declaring the cte then add a group by in there.

Shilpi Over a year ago

Can you please modify above query and iam getting following error Lookup Error - SQL Server Database Error: Create View or Function failed because no column name was specified for column 1. SELECT min(INCIDENT__ID),Stuff(( SELECT ',' + cast(CONTRACT__TITLE as varchar(2000)) FROM mycte t2 WHERE t2.[objid] = t1.[objid] and t2.[INCIDENT__ID] = t1.[INCIDENT__ID] FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') AS [AVILABLE_ENTITLEMENT] FROM mycte t1 GROUP BY t1.[objid],t1.[INCIDENT__ID]

Collectives™ on Stack Overflow

Getting error while creating View in SQL server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related