WITH Syntax in view

Question

Hi there,

Please explain the below syntax with view creation:

What is the mening of WITH

CREATE VIEW [dbo].[view_Visit]
AS
WITH ContactPerson AS (
SELECT
PersonalId,
DocupinAlias,
Date
FROM ContactTable),
GPContactProvider AS (
SELECT
PersonalId,
Date
FROM GP_Table ),

SELECT
GPCO.FacilityId,
GPCO.EncounterStartDateTime,
FROM
GPCombine1 GPCO
LEFT JOIN GPContactProvider GPP ON (rtrim(GPP.GpProviderNumber)=rtrim(GPCO.GpProviderId)

Regards

Answer 1

Hi @ASHMITP ,

Welcome to Microsoft Q&A!

The fixed syntax of common_table_expression(CTE) which specifies a temporary named result set is as below and the word 'WITH' is necessary.

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
expression_name [ ( column_name [ ,...n ] ) ]
AS
( CTE_query_definition )

This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

If we have more than one cte in one query, we could only remain the 'with' in the first cte as below:

create view viewname  
 as  
 with cte as   
 (select * from table1)  
 ,cte1 as (select * from table2)  
 select * from cte a  
 left join cte1 on a.id=b.id  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Please explain the below syntax with view creation:

You should better explain it to us; the CTE "ContactPerson" is nowhere used and the other CTE is just a different way of typing a simple query.