Блог

У меня есть следующий код SQL.Не могли бы вы помочь преобразовать следующий код SQL Server в код snowflake.Как извлечь JSON и использовать crossapply в snowflake.

     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