asked on

i need to union 2 cte if possible syntax

with cte as (select Comp_Code, Cust_ZZ_CD,
sum(mth00_AR) as sum_mth00_AR,
sum(mth00_Sales) as sum_mth00_Sales,
sum(mth01_Sales) as sum_mth01_Sales,
sum(mth02_Sales) as sum_mth02_Sales,
max(mth00_Days) as max_mth00_Days,
max(mth01_Days) as max_mth01_Days,
max(mth02_Days) as max_mth02_Days,
sum(mth03_AR) as sum_mth03_AR, -- 180
sum(mth03_Sales) as sum_mth03_Sales,  --180
sum(mth04_Sales) as sum_mth04_Sales, -- 180
sum(mth05_Sales) as sum_mth05_Sales,  -- 180
max(mth03_Days) as max_mth03_Days, -- 180
max(mth04_Days) as max_mth04_Days, -- 180
max(mth05_Days) as max_mth05_Days  --180

from [program_work].[dbo].[ARDataForDSOReporting]
group by 
Comp_Code,
Cust_ZZ_CD)

SELECT a.[Comp_Code]
      ,[Debitor]
      ,a.[Cust_ZZ_CD]
      ,[Cust_ZZ_SCD]
      ,[Cust_Credit_Account]
      ,[Cust_Coll_Segment]
      ,[Cust_Coll_Grp]
      ,[Cust_Coll_Specialist]
      ,[Cust_Name]
      ,[Track_Name]
      ,[SubTrack_Name]
      ,[CrdAcct_Name]
      ,[Cust_Coll_Spl_Name]
      ,[Bal_As_Of]
      ,[mth00_sales]
      ,[mth00_days]
      ,[mth00_AR]
      ,[mth01_sales]
      ,[mth01_days]
      ,[mth01_AR]
      ,[mth02_sales]
      ,[mth02_days]
      ,[mth02_AR]
      ,[mth03_sales]
      ,[mth03_days]
      ,[mth03_AR]
      ,[mth04_sales]
      ,[mth04_days]
      ,[mth04_AR]
      ,[mth05_sales]
      ,[mth05_days]
      ,[mth05_AR]
  FROM [program_work].[dbo].[ARDataForDSOReporting] a
  left outer join cte b on b.Comp_Code = a.Comp_Code and b.Cust_ZZ_CD = a.Cust_ZZ_CD
  
with cte1 as (select Comp_Code, Cust_ZZ_SCD,
sum(mth00_AR) as sum_mth00_AR,
sum(mth00_Sales) as sum_mth00_Sales,
sum(mth01_Sales) as sum_mth01_Sales,
sum(mth02_Sales) as sum_mth02_Sales,
max(mth00_Days) as max_mth00_Days,
max(mth01_Days) as max_mth01_Days,
max(mth02_Days) as max_mth02_Days,
sum(mth03_AR) as sum_mth03_AR, -- 180
sum(mth03_Sales) as sum_mth03_Sales,  --180
sum(mth04_Sales) as sum_mth04_Sales, -- 180
sum(mth05_Sales) as sum_mth05_Sales,  -- 180
max(mth03_Days) as max_mth03_Days, -- 180
max(mth04_Days) as max_mth04_Days, -- 180
max(mth05_Days) as max_mth05_Days  --180

from [program_work].[dbo].[ARDataForDSOReporting]
group by 
Comp_Code,
Cust_ZZ_SCD)

SELECT a1.[Comp_Code]
      ,[Debitor]
      ,a1.[Cust_ZZ_CD]
      ,[Cust_ZZ_SCD]
      ,[Cust_Credit_Account]
      ,[Cust_Coll_Segment]
      ,[Cust_Coll_Grp]
      ,[Cust_Coll_Specialist]
      ,[Cust_Name]
      ,[Track_Name]
      ,[SubTrack_Name]
      ,[CrdAcct_Name]
      ,[Cust_Coll_Spl_Name]
      ,[Bal_As_Of]
      ,[mth00_sales]
      ,[mth00_days]
      ,[mth00_AR]
      ,[mth01_sales]
      ,[mth01_days]
      ,[mth01_AR]
      ,[mth02_sales]
      ,[mth02_days]
      ,[mth02_AR]
      ,[mth03_sales]
      ,[mth03_days]
      ,[mth03_AR]
      ,[mth04_sales]
      ,[mth04_days]
      ,[mth04_AR]
      ,[mth05_sales]
      ,[mth05_days]
      ,[mth05_AR]
  FROM [program_work].[dbo].[ARDataForDSOReporting] a1
  left outer join cte1 b1 on b1.Comp_Code = a1.Comp_Code and b1.Cust_ZZ_SCD = a1.Cust_ZZ_SCD


-- This is not working
select * from cte
union
select * from
cte1

Open in new window

tHANKS
Fordraiders

Scott Pletcher

I don't see any difference between the 2 ctes. What am I missing?

slightwv (䄆 Netminder)

That said the syntax should be something like:

with cte as ( select ...)
, cte1 as (select ...)
select * from cte
union
select * from cte1

Fordraiders

ASKER

scott, et all... the ON statement is different..

cte
left outer join cte b on b.Comp_Code = a.Comp_Code and b.Cust_ZZ_CD = a.Cust_ZZ_CD

cte1
left outer join cte1 b1 on b1.Comp_Code = a1.Comp_Code and b1.Cust_ZZ_SCD = a1.Cust_ZZ_SCD

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

Scott, so does this solution NOT include CTE'S ?

Scott Pletcher

Correct, no CTEs. From what you posted, I didn't see a need for them. Of course I might have overlooked something.

Perhaps easy to test out. If my query gives you the results you want/expect, then you don't need CTEs, else you might :-).

Fordraiders

ASKER

scott:
Msg 207, Level 16, State 1, Line 18
Invalid column name 'Cust_ZZ_CD'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'Cust_ZZ_SCD'.
Msg 207, Level 16, State 1, Line 24
Invalid column name 'Cust_ZZ_Combined'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Cust_ZZ_Combined'.

Scott Pletcher

I don't see how that's possible with my specific code. And you didn't provide any directly usable sample data to test a query with, so I can't do that either.

Can you post the exact statement you ran that produced those errors? And, if possible, table DDL so I can at least create the table structure myself?

Fordraiders

ASKER

sorry, syntax error again...it worked !!
Thanks
fordraiders

Fordraiders

ASKER

Thnks all