Help with Convert SQL Server SQL to Oracle SQL — oracle-tech

    Forum Stats

  • 3,714,815 Users
  • 2,242,633 Discussions
  • 7,845,076 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Help with Convert SQL Server SQL to Oracle SQL

User_6TGRX
User_6TGRX Member Posts: 2 Green Ribbon

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

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,058 Black Diamond

    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.

    mathguyUser_6TGRX
  • User_6TGRX
    User_6TGRX Member Posts: 3

    Thanks Solomon!.This really helps

Sign In or Register to comment.