Using multiple select statements in CTE i- snowflake-CodePudding

I’m using 3 CTE’s in my SNOWFLAKE SQL query.

Using CTE 1 to query CTE 2 and CTE 3 and then joining CTE 2 and CTE 3 for Result 2

Eg. CTE 1 = Result 1 CTE 2 join CTE 3 = Result 2

Now I want to use select statements to display Result 1 and Result 2.

Which is not possible to have two select statements while using CTE. What is the alternative method to do this?

Thanks!

CodePudding user response：

You can use a union to accomplish this.

with 
  result1 as (select my_field from table1),
  result2 as (select table2.myfield 
              from table2
              inner join table 3 on table2.key = table3.key
              ),
  unioned as (
              select 'res1' as source, * from result1
              union all
              select 'res2' as source, * from result2
             )
select * from unioned

CodePudding user response：

You'll probably need to use views instead of CTEs:


create or replace view step1 
as
select *
from table1
;

create or replace view step2
as 
select *
from step1 a
join table2 b
on a.x = b.x
;

Then you can get both select * from step1 and select * from step2 to see the results.