CTE SYNTAX FOR SUBQUERY

Last Post 05 Jan 2011 01:06 PM by anwarmir. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
anwarmir
New Member
New Member

--
05 Jan 2011 01:06 PM
Hi, I have been having major performance issues with a datawarehouse query taking around 30 minutes on to complete. I want to rewrite the query to use CTE Syntax and not quite sure what i am trying to will work at all. The Query I am trying to rewrite looks lsomething like ike this. SELECT Fact_Inward_Line_Actual_Premium.DWH_Fact_Key , ( SELECT MIN(Cancel.Cancel_Date) FROM Fact_Inward_Line_Actual_Premium Fact_cd , Dim_Inward_Policy Policy_cd_outer , ( SELECT Dim_Inward_Policy.Inward_Policy_Natural_Key , MIN(CASE WHEN Dim_Transaction_ConceptOne.ConceptOne_Resolved_Date > Dim_Transaction_ConceptOne.ConceptOne_Revision_Effective_Date THEN Dim_Transaction_ConceptOne.ConceptOne_Resolved_Date ELSE Dim_Transaction_ConceptOne.ConceptOne_Revision_Effective_Date END) AS Cancel_Date FROM Fact_Inward_Line_Actual_Premium Min_Fact INNER JOIN Dim_Inward_Policy ON Min_Fact.Inward_Policy_Key = Dim_Inward_Policy.Inward_Policy_Key INNER JOIN Dim_Transaction_ConceptOne ON min_fact.Transaction_Key = Dim_Transaction_ConceptOne.Transaction_Key WHERE Dim_Transaction_ConceptOne.ConceptOne_Procedure_Object_Code = 'Cancel' AND Min_Fact.Transaction_Effective_Date_Key >= Fact_Inward_Line_Actual_Premium.Transaction_Effective_Date_Key GROUP BY Dim_Inward_Policy.Inward_Policy_Natural_Key ) Cancel WHERE Fact_cd.Inward_policy_key = Policy_cd_outer.inward_policy_key AND Cancel.inward_policy_natural_key = Policy_cd_outer.inward_policy_natural_key AND Fact_cd.Inward_Parti***tion_Line_Key = Fact_Inward_Line_Actual_Premium.Inward_Parti***tion_Line_Key ) AS cancel_date from Fact_Inward_Line_Actual_Premium How I think This should be implemented is by using multiple CTES with a final select ??? However, my sub query won't allow me to contain another sub query references the outer CTE. ANY help would be appreciate WITH Fact_Inward_Line ( DWH_Fact_Key ) ( SELECT Fact_Inward_Line_Actual_Premium.DWH_Fact_Key , ( SELECT MIN(Cancel.Cancel_Date) FROM Fact_Inward_Line F , Fact_Inward_Line_Actual_Premium Fact_cd , Dim_Inward_Policy Policy_cd_outer , ( SELECT Dim_Inward_Policy.Inward_Policy_Natural_Key , MIN(CASE WHEN Dim_Transaction_ConceptOne.ConceptOne_Resolved_Date > Dim_Transaction_ConceptOne.ConceptOne_Revision_Effective_Date THEN Dim_Transaction_ConceptOne.ConceptOne_Resolved_Date ELSE Dim_Transaction_ConceptOne.ConceptOne_Revision_Effective_Date END) AS Cancel_Date FROM Fact_Inward_Line_Actual_Premium Min_Fact INNER JOIN Dim_Inward_Policy ON Min_Fact.Inward_Policy_Key = Dim_Inward_Policy.Inward_Policy_Key INNER JOIN Dim_Transaction_ConceptOne ON min_fact.Transaction_Key = Dim_Transaction_ConceptOne.Transaction_Key WHERE Dim_Transaction_ConceptOne.ConceptOne_Procedure_Object_Code = 'Cancel' --AND Min_Fact.Transaction_Effective_Date_Key >= dbo.Fact_Inward_Line_Actual_Premium.Transaction_Key GROUP BY Dim_Inward_Policy.Inward_Policy_Natural_Key ) Cancel WHERE F.DWH_Fact_Key = Fact_cd.DWH_Fact_Key AND Fact_cd.Inward_policy_key = Policy_cd_outer.inward_policy_key AND Cancel.inward_policy_natural_key = Policy_cd_outer.inward_policy_natural_key AND Fact_cd.Inward_Parti***tion_Line_Key = F.Inward_Parti***tion_Line_Key
You are not authorized to post a reply.

Acceptable Use Policy