Refactoring Query with CTE

Question

Hi Community,

Can someone help refactor this code to work without CTE?

Unfortunately, the platform that I use for T-SQL queries does not support CTE's

The code is as follows:

 ;WITH CTE1 AS  
 (  
  SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM tableone  
 ),CTE2 AS  
 (  
  SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM tabletwo  
 )  
 SELECT C1.Id,C1.SinkCreatedOn,C1.SinkModifiedOn,C1.statecode,C1.statuscode  
       ,CASE WHEN C1.ts_primarysecondaryfocus<>ISNULL(C2.ts_primarysecondaryfocus,'')THEN C2.ts_primarysecondaryfocus ELSE C1.ts_primarysecondaryfocus END AS ts_primarysecondaryfocus  
       ,C1.customertypecode,C1.address1_addresstypecode,C1.accountclassificationcode,C1.ts_easeofworking   
       ,CASE WHEN C1.ts_ukrow<>ISNULL(C2.ts_ukrow,'')THEN C2.ts_ukrow ELSE C1.ts_ukrow END AS ts_ukrow   
       ,C1.preferredappointmenttimecode,C1.xpd_relationshipstatus,C1.ts_relationship  
 FROM CTE1 C1 LEFT JOIN CTE2 C2 ON C1.RowNum = C2.RowNum  

Answer 1

SELECT C1.Id,C1.SinkCreatedOn,C1.SinkModifiedOn,C1.statecode,C1.statuscode  
      ,CASE WHEN C1.ts_primarysecondaryfocus<>ISNULL(C2.ts_primarysecondaryfocus,'')THEN C2.ts_primarysecondaryfocus ELSE C1.ts_primarysecondaryfocus END AS ts_primarysecondaryfocus  
      ,C1.customertypecode,C1.address1_addresstypecode,C1.accountclassificationcode,C1.ts_easeofworking   
      ,CASE WHEN C1.ts_ukrow<>ISNULL(C2.ts_ukrow,'')THEN C2.ts_ukrow ELSE C1.ts_ukrow END AS ts_ukrow   
      ,C1.preferredappointmenttimecode,C1.xpd_relationshipstatus,C1.ts_relationship  
FROM (SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM tableone) C1   
LEFT JOIN (SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM tabletwo) C2 ON C1.RowNum = C2.RowNum  

Tom

Answer 2

Hi,@Carlton Patterson
Generally , instead of cte , we use sub-queries and gradually filled tables to perform brute force methods . The sub-queries given by tom will perform more efficiently than gradually filling tables without using cte . Refer to this link.

Bert Zhou

Answer 3

If you cannot use a CTE, you must not be using Microsoft SQL Server. You will likely get a better answer from a forum appropriate to your database engine.