WITH Syntax in view

ASHMITP 141 Reputation points
2021-04-29T00:24:59.15+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,321 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,524 questions
{count} votes

2 answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-04-29T01:37:07.223+00:00

    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.

    0 comments No comments

  2. Olaf Helper 39,181 Reputation points
    2021-04-29T05:28:10.98+00:00

    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.

    0 comments No comments