How To Refactor Code with CTE Pt3

Question

Post reply

How To Refactor Code with CTE Pt3

carlton 84646

Ten Centuries

Points: 1026

July 14, 2022 at 4:33 pm

#4063393

Hi Community,

I have tried refactoring code to execute with CTE as described on this site but I keep on getting errors:

The code that I'm trying to refactor is:

WITH CTE1 AS
(
SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM accountv41
),CTE2 AS
(
SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM optionsetmetadatav3
)
SELECT C1.SinkCreatedOn,C1.SinkModifiedOn,C1.statecode,C1.statuscode
,CASE WHEN ISNULL(C1.ts_primarysecondaryfocus,'') <> ISNULL(C2.ts_primarysecondaryfocus,'') THEN C2.ts_primarysecondaryfocus ELSE C1.ts_primarysecondaryfocus END AS ts_primarysecondaryfocus
FROM CTE1 C1 LEFT JOIN CTE2 C2 ON C1.RowNum = C2.RowNum

I'm trying to recode the above with using CTE.

Any thoughts?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

My first thought is that you shouldn't refactor code just to use a shiny new toy.
My second thought is that it already uses a CTE (two in fact), so your refactoring is already complete.
My third thought is that it always amazes me that people think that error messages have so little relevance to troubleshooting a problem that they don't include them in their posts.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

following the above... if you hide the error message then your code is fine and you are good to go (for a beer)

Ed B Hall of Fame Points: 3665 More actions · Answer 3

This appears to be a follow-up to this post. https://www.sqlservercentral.com/forums/topic/how-to-refactor-code-with-cte

The OP states that cte's are not supported. A solution using derived tables was offered, but not acknowledged. If the platform does not support cte's maybe it is another database software, in which case maybe the ISNULL needs to be replaced with NVL or some other equivalent function.

carlton84646: Can you tell us what database platform you are using and whether you are trying to eliminate cte's or make them work? Do you have a syntax error? or is the query running, but returning the wrong results?

Jonathan AC Roberts SSCoach Points: 18347 More actions · Answer 4

carlton 84646 wrote:

Hi Community,
I have tried refactoring code to execute with CTE as described on this site but I keep on getting errors:
The code that I'm trying to refactor is:
WITH CTE1 AS
(
SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM accountv41
),CTE2 AS
(
SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM optionsetmetadatav3
)
SELECT C1.SinkCreatedOn,C1.SinkModifiedOn,C1.statecode,C1.statuscode
,CASE WHEN ISNULL(C1.ts_primarysecondaryfocus,'') <> ISNULL(C2.ts_primarysecondaryfocus,'') THEN C2.ts_primarysecondaryfocus ELSE C1.ts_primarysecondaryfocus END AS ts_primarysecondaryfocus
FROM CTE1 C1 LEFT JOIN CTE2 C2 ON C1.RowNum = C2.RowNum
I'm trying to recode the above with using CTE.
Any thoughts?

Did you really mean "I'm trying to recode the above without using a CTE."?

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 5

carlton 84646

Ten Centuries

Points: 1026

July 15, 2022 at 7:09 am

#4063674

Jonathan

That is correct.

thanks

frederico_fonseca SSCoach Points: 16154 More actions · Answer 6

before we waste more time with this can you tell us what is the db engine/software you are using and why using a CTE is not possible - and what is the darn error you are getting.

Ed B Hall of Fame Points: 3665 More actions · Answer 7

frederico_fonseca wrote:

before we waste more time with this can you tell us what is the db engine/software you are using and why using a CTE is not possible - and what is the darn error you are getting.

A derived table solution was provided in a previous post. Slightly different table names, but same concept.

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