Help with Convert SQL Server SQL to Oracle SQL

User_6TGRX

Hi All,

Hope you are doing well!..I am trying to convert the following SQL server code to oracle SQL as we have migrated to oracle SQL...Can you please help with the same..

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

Solomon Yakobson

It looks like code you posted is missing first line which from what I can tell should be WITH Q1 AS (. Anyway, something like:

with q1 as (
            select  vid,
                    ctextid,
                    createdby,
                    created,
                    description,
                    p.dcode, 
                    dense_rank() over(partition by vid,ctextid order by created) rn
              from  cte3,
                    json_table(
                               info,
                               '$'
                               columns(
                                       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) vid,
                    coalesce(r1.ctextid,r2.ctextid) ctextid, 
                    r1.description description1,
                    r1.dcode dcode1,
                    r2.description description2,
                    r2.dcode dcode2,
              from      r1 
                    full outer join
                        r2
                      on     r2.vid = r1.vid 
                         and
                             r2.ctextid = r1.ctextid 
                         and 
                             r2.dDescription = r1.description
           )
select  vid,
        ctextid,
        (
         -- no need for top(1) since table r1 has only one row per vid,ctextid
         select  createdby 
           from  r1
           where r1.vid = t.vid
             and r1.ctextid = t.ctextid
           -- no need for top(1) since table r1 has only one row per vid,ctextid but if you
           -- want you can uncomment next line which is SQL Server top(1) replacemnet in Oracle
           -- fetch first 1 row only
        ) codername,
        -- I left coalesce but it is useless here since unlike SQL Server Oracle treats '' as NULL
        coalesce(listagg(case when dcode1 <> dcode2 then dcode1 end,', ') within group(order by 1),'') correctedcode, 
        coalesce(listagg(case when dcode2 is null then dcode1 end,', ') within group(order by 1),'') deletedcode, 
        coalesce(listagg(case when dcode1 is null then dcode2 end,', ') within group(order by 1),'') addedcode 
  from  q2 as t
  group by vid,
           ctextid
  order by vid
/

SY.

User_6TGRX

Thanks Solomon!.This really helps