What is the SQL With clause (known as CTE) ?

About

The with sql clause permits to define inline views (ie without creating them).

These views are also commonly known as CTE (common table expression) because a query (hence a view) returns a logical table.

Example

WITH
  myFirstView AS
  (
    SELECT
      Table1.Code       ,
      Table1.Description,
      Table1.Action     ,
      Table2.Team
    FROM
      Table1,
      Table2
    WHERE
      Table1.Id=Table2.Id
    AND
      Table1.Id <>-1
    AND
      Table1.Id <>-2
  )
  ,
  mySecondView AS
  (
    SELECT distinct Code  FROM myFirstView 
  )
SELECT
  *
FROM
  myFirstView
WHERE
  Code IN
  (
    SELECT Code FROM myFirstView HAVING COUNT(*) > 1 GROUP BY Code
  )
ORDER BY
  Code