Any thoughts on this guys/girls?

Hi Scott,

Sorry for the delayed response.

I tried your suggestion with ORDER BY C1.RowNum

But I'm still getting NULLs in the prioritycode on table optionsettransposedall_why

SELECT C1.Id
,C1.SinkCreatedOn
,C1.SinkModifiedOn
,C1.statecode
,C1.statuscode,
CASE WHEN ISNULL(CAST(C1.prioritycode AS...

I did an orderby with the following full code, and still nothing

SELECT
  C1.Id
 ,C1.SinkCreatedOn
 ,C1.SinkModifiedOn
 ,C1.statecode
 ,C1.statuscode
 ,CASE
    WHEN ISNULL(CAST(C1.prioritycode AS VARCHAR(50)), '')...

I'm still confused!

Plus the code are identical, its just the table that I see that is different

I thought I included order by here:

SELECT *,ROW_NUMBER()OVER(ORDER BY prioritycode)

Jonathan

That is correct.

thanks

Why did you delete your code

Still getting NULLs.

I really haven't got a clue at this stage

Thanks for trying

Getting the following error:

Incorrect syntax near the keyword 'null'.

I'll try anything at this stage ... going to try it now.

Any further thoughts?

I should point out that account and OptionsetMetadata are both views on Azure Synapse, but I'm not sure if that would make a difference??

This is soooooo strange

Something very strange is going on....

When I execute the code

;WITH CTE1 AS
  (
   SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM [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 CAST(C1.ts_primarysecondaryfocus AS VARCHAR(100))<>ISNULL(CAST(C2.ts_primarysecondaryfocus as VARCHAR(100)),'')THEN C2.ts_primarysecondaryfocus ELSE C1.ts_primarysecondaryfocus END AS ts_primarysecondaryfocus
        ,C1.customertypecode,C1.address1_addresstypecode,C1.accountclassificationcode,C1.ts_easeofworking 
        ,CASE WHEN CAST(C1.ts_ukrow AS VARCHAR(100)) <>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

On Azure Synapse SQL DW I get NULLs, where I should be getting data i.e the value TBC,...