HOW TO USE COMMON TABLE EXPRESSIONS

Ankit Singh

Total Post:341

Points:2389
Posted by  Ankit Singh
 630  View(s)
Ratings:
Rate this:
We want to To Use Common Table Expressions. How will do this please help me.
  1. aditya kumar Patel

    Post:254

    Points:1810
    Re: How To Use Common Table Expressions

    A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

    CREATE TABLE [CUSTOMER]

    (

    [CUST_ID] [bigint] IDENTITY(1,1)PRIMARY KEY,

    [CUST_NAME] [varchar](200) NULL,

    [CUST_EMAILID] [varchar](100) NULL,

    [CUST_PHONE] [varchar](20) NULL,

    [CUST_ADDRESS] [varchar](800) NULL

    )

    SELECT * FROM [CUSTOMER]

     

    CREATE TABLE [ORDER]

    (

    [ORD_ID] [bigint] IDENTITY(1,1) PRIMARY KEY,

    [CUST_ID] [bigint] NULL,

    [PRODUCT_ID] [bigint] NULL,

    [ORD_DATE] [date] NULL

    )

    SELECT * FROM [ORDER]

     

    CREATE TABLE [PRODUCT]

    (

    [PRODUCT_ID] [bigint] IDENTITY(1,1) PRIMARY KEY,

    [PRODUCT_NAME] [varchar](200) NULL,

    [PRICE] [decimal](10, 2) NULL

    )

    SELECT * FROM [PRODUCT]

     

     

    ;With CTE(ORD_ID, ORD_DATE, CUST_NAME,CUST_EMAILID,CUST_PHONE,PRODUCT_NAME,PRICE)

    AS

    (

    SELECT O.ORD_ID,O.ORD_DATE,C.CUST_NAME,C.CUST_EMAILID,C.CUST_PHONE,P.PRODUCT_NAME,P.PRICE FROM

    [ORDER] AS [O] INNER JOIN CUSTOMER AS [C] ON O.CUST_ID=C.CUST_ID

    INNER JOIN PRODUCT AS [P] ON P.PRODUCT_ID=O.PRODUCT_ID

    )

    SELECT * FROM CTE WHERE PRODUCT_NAME='COMPUTER' --Using CTE

     

     

      Modified On Mar-10-2016 01:15:31 AM

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!