SQL - How to combine two table in Common table expression (cte)

show an example of your syntax ... if not the actual SQL.

Same here. Doubt on the error itself :)

Have a look at the example below.  I have done a UNION inside a CTE and, just in case, a UNION of two CTE's.  Without the SQL and/or the actual error message ... I would guessing what at the problem and solution would be.

IF OBJECT_ID('tempdb..#tempTable1') IS NOT NULL
	DROP TABLE #tempTable1;
create table #tempTable1 (
    id  int,
    val varchar(20)
);
insert into #tempTable1 values (1,'a'),(2,'b'),(3,'c'),(4,'d');
IF OBJECT_ID('tempdb..#tempTable2') IS NOT NULL
	DROP TABLE #tempTable2;
create table #tempTable2 (
    id  int,
    val varchar(20)
);
insert into #tempTable2 values (1,'x'),(2,'y'),(3,'z');

;with cte_with_union as (
select id, val
from #tempTable1
union all
select id, val
from #tempTable2
)
select *
  from cte_with_union;

;with cte_1 as (
select id, val
from #tempTable1
), cte_2 as (
select id, val
from #tempTable2
)
select *
  from cte_1
union all
select *
  from cte_2;

Select allOpen in new window

This is the statement I try to make.  If I execute only the SQL statement within the cte. It works.  But if I have cte as below.  I got this error
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '{'.
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ')'.
----------------------------------------------------------------------------
with cte As
{
SELECT [Sales Rep]
      FROM [SalesRepsTable]
      GROUP BY [Sales Rep]
      Having min([Rep Status]) < 1
      
union      
-- Give me a list of INactive advisor
      SELECT [Sales Rep]
      FROM [SalesRepsTable]
      GROUP BY [Sales Rep]
      Having min([Rep Status]) = 1
)
select * from cte

Thanks.