How to Refactor Code with CTE

• carlton 84646

  Ten Centuries

  Points: 1026

  More actions

  July 11, 2022 at 12:57 pm

  #4061619

  Hello Community,

  Can someone show me how to refactor the following code with CTE's? My platform doesn't support CTE's

   

   

  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

  Thank you

   

  • This topic was modified 2 years, 5 months ago by  Steve Jones - SSC Editor.

• planetmatt

  SSCrazy

  Points: 2207

  More actions

  July 11, 2022 at 1:32 pm

  #4061631

  You can replace each of the 2 CTEs with Derived Tables still using Row_Number() to create your surrogate key

  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