Can i run more then one main select statments on the 2 with tables?-CodePudding

Hay All,

is it possible to run more than 1 select statement after using with? first select statement works fine, as soon as i add another select statement i got a error.

with 
a as (select a,b,c from Table1 with(readuncommitted)),
b as (select d,e,f from Table2 with(readuncommitted))

select * from a
select * from b

expected output: Table 1 a Table 2 b

CodePudding user response：

Well the way CTEs will behave is that they will only be in scope for the first query, but not the second. You could perhaps do a union query here:

SELECT a, b, c, 'Table1' AS src FROM a
UNION ALL
SELECT d, e, f, 'Table2' FROM b;

Or, you could move the b CTE to before the second query:

WITH a AS ( 
    SELECT a, b, c
    FROM Table1
    WITH(readuncommitted)
)

SELECT * FROM a;

WITH b AS ( 
    SELECT d, e, f
    FROM Table2
    WITH(readuncommitted)
)

SELECT * FROM b;

CodePudding user response：

hay DasD, You can not use multiple select for cte, but you can use more than one CTE like this.

with 
a as (select a,b,c from Table1 with(readuncommitted)),
b as (select d,e,f from Table2 with(readuncommitted))

select * from a,b

CodePudding user response：

You have to explain to the database, what you wantfrom bith tables.

as both have the same structure you can use UNION to join them vertically

with 
a as (select a,b,c from Table1 with(readuncommitted)),
b as (select d,e,f from Table2 with(readuncommitted))

select * from a
UNION
select * from b

CodePudding user response：

From the docs: "A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns."

Source