SQL with Clause Example

How to create a table using With clause in SQL

This is not valid syntax for sql server. you can either create a table using CREATE TABLE and specifying the column names and types, or you can do a SELECT INTO statement including data.

Approach 1 : Create the table and then populate:

CREATE TABLE SalesOrdersPerYear 
( SalesPersonID int, BaseSalary float)
;

WITH Sales_CTE (SalesPersonID, BaseSalary)  
AS  
(  
    SELECT SALES_PERSON.SALES_PERSON_ID, SALES_PERSON.BASE_SALARY  
    FROM SALES_PERSON  
    WHERE SALES_PERSON_ID IS NOT NULL 
)  
insert into SalesOrdersPerYear  
SELECT SalesPersonID, BaseSalary AS TotalSales  
FROM Sales_CTE   
ORDER BY SalesPersonID, BaseSalary;  

Approach 2 - all in one step

WITH Sales_CTE (SalesPersonID, BaseSalary)  
AS  
(  
    SELECT SALES_PERSON.SALES_PERSON_ID, SALES_PERSON.BASE_SALARY  
    FROM SALES_PERSON  
    WHERE SALES_PERSON_ID IS NOT NULL 
)  
select  SalesPersonID, BaseSalary AS TotalSales  
into SalesOrdersPerYear
FROM Sales_CTE   
ORDER BY SalesPersonID, BaseSalary;  

Use approach 1 when you need to specify more about the table (primary keys, indexes, foregin keys etc.

Use approach 2 for things that are more temporary. (you would normally use a temporary table such as #SalesOrdersPerYear here).

Either way, the data is now stored in your table, and you can use it again.

Using temporary tables:

-- Check for existence and drop first to avoid errors if it already exists.
if OBJECT_ID('tempdb..#SalesOrdersPerYear') is not null
    drop table #SalesOrdersPerYear

WITH Sales_CTE (SalesPersonID, BaseSalary)  
AS  
(  
    SELECT SALES_PERSON.SALES_PERSON_ID, SALES_PERSON.BASE_SALARY  
    FROM SALES_PERSON  
    WHERE SALES_PERSON_ID IS NOT NULL 
)  
select  SalesPersonID, BaseSalary AS TotalSales  
into #SalesOrdersPerYear
FROM Sales_CTE   
ORDER BY SalesPersonID, BaseSalary;  

You could also define it as a table variable, which is a bit of a cross between the approaches:

declare @SalesOrdersPerYear table
( SalesPersonID int, BaseSalary float)
;

WITH Sales_CTE (SalesPersonID, BaseSalary)  
AS  
(  
    SELECT SALES_PERSON.SALES_PERSON_ID, SALES_PERSON.BASE_SALARY  
    FROM SALES_PERSON  
    WHERE SALES_PERSON_ID IS NOT NULL 
)  
insert into @SalesOrdersPerYear  
SELECT SalesPersonID, BaseSalary AS TotalSales  
FROM Sales_CTE   
ORDER BY SalesPersonID, BaseSalary;  

This option will only persist with this batch, and does not need dropping - just like any other variable.

Query on WITH Clause in SQL Server

The query which you are trying to execute is called Common Table Expression and the the syntax which you are using is not the proper way of using it ie, you cannot use the WITH clause inside the select statement.

with projects as (

select * from projectdetails

)
select * from projects

The above query is fine and will work and the next query which you posted is neither correct and neither it is making sense as it is looking as if you are trying to do the same thing as above.

Multiple SQL WITH clauses - why not?

You should be able to have 2 WITH clauses, you just need a semicolon after the first one.

With A as (Select P,Q,R from X union all Select P,Q,R from Y)
Insert into File1
Select * from A;


With B as (Select S,T,U from Z)
Insert into File2
Select * from B

This is the error you would get from SQL if you are trying to do two without semicolon:

Msg 336, Level 15, State 1, Line 6
Incorrect syntax near 'B'. If this is intended to be a common table expression, you need to explicitly terminate the previous statement with a semi-colon.

Using SQL WITH Clause in PHP

One possible explanation is that there is no column RowNum in your DataSet CTE. Just correct your T-SQL statement and include RowNum column.

<?php

$sql = "WITH DATASET AS";
$sql .= "(";
$sql .= "SELECT 
        -- Include RowNum column here ...
        --ROW_NUMBER() OVER (...) As RowNum,
        CASE WHEN CHARINDEX('%',\"Name\")>0 THEN SUBSTRING(\"Name\",1,CHARINDEX('_',\"Name\")-1) ELSE \"Name\" END as \"Full Package Name\",
        CASE WHEN CHARINDEX('_',\"Name\")> 0 THEN (select left(\"Name\", charindex('_', \"Name\", charindex('_', \"Name\", charindex('_', \"Name\", charindex('_', \"Name\", charindex('_', \"Name\" )+1)+1)+1)+1)-1)) ELSE \"Name\" END as \"Package Name\",
        CASE WHEN CHARINDEX('_',\"Name\")> 0 THEN SUBSTRING(\"Name\", CHARINDEX('_',\"Name\",CHARINDEX('_',\"Name\", (CHARINDEX('_',\"Name\",(CHARINDEX('_',\"Name\",(CHARINDEX('_',\"Name\")+1))+1))+1))+1)+1,20) ELSE \"Name\" END as 'Version'
        FROM vItem WHERE \"Name\" LIKE 'pkg%'
        AND \"Name\" NOT LIKE 'XPF %'
        AND \"Name\" NOT LIKE 'pkgc%'
        AND \"Name\" NOT LIKE 'PKG_ADD_%'
        AND \"Name\" NOT LIKE '%_SWV%'
        AND \"Name\" NOT LIKE '% - %'
        AND \"Name\" NOT LIKE '%_BETA'
        AND \"Name\" NOT LIKE '%SV1'
        AND \"Name\" NOT LIKE '% Detection%'
        AND CreatedDate > '2016-1-01 01:50:58.120'";
$sql .= ")";
$sql .= "SELECT * FROM Dataset WHERE RowNum <= 3";

...
?>

How do you use the WITH clause in MySQL?

MySQL prior to version 8.0 doesn't support the WITH clause (CTE in SQL Server parlance; Subquery Factoring in Oracle), so you are left with using:

• TEMPORARY tables
• DERIVED tables
• inline views (effectively what the WITH clause represents - they are interchangeable)

The request for the feature dates back to 2006.

As mentioned, you provided a poor example - there's no need to perform a subselect if you aren't altering the output of the columns in any way:

  SELECT * 
    FROM ARTICLE t
    JOIN USERINFO ui ON ui.user_userid = t.article_ownerid
    JOIN CATEGORY c ON c.catid =  t.article_categoryid
   WHERE t.published_ind = 0
ORDER BY t.article_date DESC 
   LIMIT 1, 3

Here's a better example:

SELECT t.name,
       t.num
  FROM TABLE t
  JOIN (SELECT c.id
               COUNT(*) 'num'
          FROM TABLE c
         WHERE c.column = 'a'
      GROUP BY c.id) ta ON ta.id = t.id

Generate a WITH clause/UNIONs from a SELECT

It would be easier to use xmltable or (json_table for Oracle 12+) for such purposes.

Example with xmltable:

1. Just aggregate all the required data into xmltype:
   you can use xmltype(cursor(select...from...)):

select xmltype(cursor(select * from test)) xml from dual;

or dbms_xmlgen.getxmltype(query_string):

select dbms_xmlgen.getxmltype('select * from test') xml from dual;

2. then you can use the returned XML with

xmltable('/ROWSET/ROW' passing xmltype(your_xml) columns ...)

Example:

select *
from xmltable(
        '/ROWSET/ROW'
        passing xmltype(q'[<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <ASSET_ID>10</ASSET_ID>
  <VERTEX_NUM>1</VERTEX_NUM>
  <X>118.56</X>
  <Y>3.8</Y>
 </ROW>
 <ROW>
  <ASSET_ID>10</ASSET_ID>
  <VERTEX_NUM>2</VERTEX_NUM>
  <X>118.62</X>
  <Y>1.03</Y>
 </ROW>
</ROWSET>
        ]')
    columns 
        asset_id,vertex_num,x,y
) test

Full example on DBFiddle: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=036b718f2b18df898c3e3de722c97378

---

Related Topics