Link to home
Start Free TrialLog in
Avatar of Fordraiders
FordraidersFlag for United States of America

asked on

using 4 CTE's and trying to run query is there a faster way

using 4 CTE and trying to run query..
is there a better and faster way  ?

Need distinct [Debitor]



with cte1 ([Debitor],[Comp_Code],[Cust_ZZNA_CD],[Cust_ZZNA_SCD],[DSO]) as
(SELECT distinct [Debitor]
       ,[Comp_Code]
      ,[Cust_ZZNA_CD]
      ,[Cust_ZZNA_SCD]
      ,[DSO] 
      FROM [program_work].[dbo].[VW_VIEW_DSO_180] dso180)
      ,
 cte2 ([Debitor],[Comp_Code],[Cust_ZZNA_CD],[Cust_ZZNA_SCD],[DSO]) as
(SELECT distinct [Debitor]
       ,[Comp_Code]
      ,[Cust_ZZNA_CD]
      ,[Cust_ZZNA_SCD]
      ,[DSO] 
      FROM [program_work].[dbo].[VW_VIEW_DSO_270] dso270)
      ,
 cte3 ([Debitor],[Comp_Code],[Cust_ZZNA_CD],[Cust_ZZNA_SCD],[DSO]) as
(SELECT distinct [Debitor]
       ,[Comp_Code]
      ,[Cust_ZZNA_CD]
      ,[Cust_ZZNA_SCD]
      ,[DSO] 
      FROM [program_work.[dbo].[VW_VIEW_DSO_360] dso360)
      ,
      
 cte4 ([Debitor],[Comp_Code],[Cust_ZZNA_CD],[Cust_ZZNA_SCD],[DSO]) as
(SELECT distinct [Debitor]
       ,[Comp_Code]
      ,[Cust_ZZNA_CD]
      ,[Cust_ZZNA_SCD]
      ,[DSO] 
      FROM [program_work.[dbo].[VW_VIEW_DSO_450] dso450)
      
SELECT distinct dso180.[Debitor]
       ,dso180.[Comp_Code]
      ,dso180.[Cust_ZZNA_CD]
      ,dso180.[Cust_ZZNA_SCD]
      ,dso180.[DSO] as dso_180
      ,cte2.dso as dso_270
      ,cte3.dso as dso_360
      ,cte4.DSO as dso_450
  FROM [program_work].[dbo].[VW_VIEW_DSO_180] dso180

left outer join
  cte2
  on
 dso180.Debitor = cte2.Debitor
  
left outer join
 cte3
  on
  dso180.Debitor = cte3.Debitor
  
  left outer join
  cte4
  on
  dso180.Debitor = cte4.Debitor

 where dso180.[Cust_ZZNA_CD] is not null

Open in new window




Thanks
fordraiders
Avatar of Bill Prew
Bill Prew

Hard to know without knowing your data, but perhaps something like this:

SELECT DISTINCT dso180.Debitor
      ,dso180.Comp_Code
      ,dso180.Cust_ZZNA_CD
      ,dso180.Cust_ZZNA_SCD
      ,dso180.DSO AS dso_180
      ,dso270.DSO AS dso_270
      ,dso360.DSO AS dso_360
      ,dso450.DSO AS dso_450
FROM program_work.dbo.VW_VIEW_DSO_180 dso180
LEFT OUTER JOIN program_work.dbo.VW_VIEW_DSO_270 dso270 ON dso270.Debitor = dso180.Debitor
LEFT OUTER JOIN program_work.dbo.VW_VIEW_DSO_360 dso360 ON dso360.Debitor = dso180.Debitor
LEFT OUTER JOIN program_work.dbo.VW_VIEW_DSO_450 dso450 ON dso450.Debitor = dso180.Debitor
WHERE dso180.Cust_ZZNA_CD IS NOT NULL

Open in new window


»bp
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Fordraiders

ASKER

Thanks folks as always !!
I suspect the proposed left joins require use of the row_number() output:

LEFT OUTER JOIN cte2 ON cte1.Debitor = cte2.Debitor AND cte2.row_num = 1
LEFT OUTER JOIN cte3 ON cte1.Debitor = cte3.Debitor AND cte3.row_num = 1
LEFT OUTER JOIN cte4 ON cte1.Debitor = cte4.Debitor AND cte4.row_num = 1