Refactor Code with CTE Part 2

Question

Hello Forum,

I have been trying to learn how to refactor code with help from Bert https://learn.microsoft.com/en-us/answers/questions/920463/refactoring-query-with-cte.html

However, its not working out for me.

Can someone help me refactor this code to work without CTE

WITH CTE1 AS  
   (  
    SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM [dataverse_montagu_org5a2bcccf].[dbo].[account]  
   ),CTE2 AS  
   (  
    SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM [dbo].[OptionsetMetadata]  
   )  
   SELECT C1.Id,C1.SinkCreatedOn,C1.SinkModifiedOn,C1.statecode,C1.statuscode,  
    CASE WHEN ISNULL(CAST(C1.ts_primarysecondaryfocus AS VARCHAR(50)),'') <> ISNULL(CAST(C2.ts_primarysecondaryfocus AS VARCHAR(50)),'') THEN CAST(C2.ts_primarysecondaryfocus AS VARCHAR(50)) ELSE CAST(C1.ts_primarysecondaryfocus AS VARCHAR(50)) END AS ts_primarysecondaryfocus  
    ,C1.customertypecode,C1.address1_addresstypecode,C1.accountclassificationcode,C1.ts_easeofworking  
    ,CASE WHEN ISNULL(CAST(C1.ts_ukrow AS VARCHAR(50)),'') <> ISNULL(CAST(C2.ts_ukrow AS VARCHAR(50)),'') THEN CAST(C2.ts_ukrow AS VARCHAR(50)) ELSE CAST(C1.ts_ukrow AS VARCHAR(50)) END AS ts_ukrow  
   FROM CTE1 C1 LEFT JOIN CTE2 C2 ON C1.RowNum = C2.RowNum  

Answer 1

Same way as Tom showed in the other thread, e.g.

    SELECT C1.Id,C1.SinkCreatedOn,C1.SinkModifiedOn,C1.statecode,C1.statuscode,  
     CASE WHEN ISNULL(CAST(C1.ts_primarysecondaryfocus AS VARCHAR(50)),'') <> ISNULL(CAST(C2.ts_primarysecondaryfocus AS VARCHAR(50)),'') THEN CAST(C2.ts_primarysecondaryfocus AS VARCHAR(50)) ELSE CAST(C1.ts_primarysecondaryfocus AS VARCHAR(50)) END AS ts_primarysecondaryfocus  
     ,C1.customertypecode,C1.address1_addresstypecode,C1.accountclassificationcode,C1.ts_easeofworking  
     ,CASE WHEN ISNULL(CAST(C1.ts_ukrow AS VARCHAR(50)),'') <> ISNULL(CAST(C2.ts_ukrow AS VARCHAR(50)),'') THEN CAST(C2.ts_ukrow AS VARCHAR(50)) ELSE CAST(C1.ts_ukrow AS VARCHAR(50)) END AS ts_ukrow  
    FROM (SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM [dataverse_montagu_org5a2bcccf].[dbo].[account]) C1 LEFT JOIN ( SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM [dbo].[OptionsetMetadata]) C2 ON C1.RowNum = C2.RowNum  

Answer 2

Just to be clear.

A CTE is simply a formatting convenience. It is not the performance improvement.

It is basically a string macro replacing

FROM cte  

with

FROM ({your CTE code}) as cte