Convert SQL server code to Snowflake SQL code

Hellothere8028 821 Reputation points
2020-11-26T13:24:41.197+00:00

Hi All,

Hope you are doing well!..I am trying to convert the following SQL server code to Snowflake SQL ...Can you please help me here..

select vid, ctextid, createdby, Created, description, p.dCode,
dense_rank() over (partition by vid,ctextid order by Created) as rn
from cte3
cross apply openjson(info) with
(
dCode varchar(30) '$.dCode',
Description varchar(30) '$.description'
) p
),
R1 as
(
select * from Q1 where rn = 1
),
R2 as
(
select * from Q1 where rn = 2
),
Q2 as
(
select coalesce(R1.vid, R2.vid) as vid, coalesce(R1.ctextid, R2.ctextid) as ctextid,
R1.Description as Description1, R1.dCode as dCode1,
R2.Description as Description2, R2.dCode as dCode2
from R1
full outer join R2
on R2.vid = R1.vid
and R2.ctextid = R1.ctextid
and R2.[Description] = R1.[Description]
)
select vid, ctextid,
(select top(1) createdby from R1 where vid = t.vid and ctextid = t.ctextid) as codername,
coalesce( string_agg(case when dCode1 <> dCode2 then dCode1 end, ', '), '') as correctedcode,
coalesce( string_agg(case when dCode2 is null then dCode1 end, ', '), '') as deletedcode,
coalesce( string_agg(case when dCode1 is null then dCode2 end, ', '), '') as addedcode
from Q2 as t
group by vid, ctextid
order by vid

Thanks,
Arun

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,545 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-11-27T01:05:31.543+00:00

    Hi @Hellothere8028 ,

    We recommend you to post this question on Snowflake related forums as below instead.

    Snowflake Community
    Questions tagged (snowflake-cloud-data-platform)

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Hot issues November--What can I do if my transaction log is full?
    Hot issues November--How to convert Profiler trace into a SQL Server table


0 additional answers

Sort by: Most helpful