I've been tasked with converting a process from Oracle to Sql Server 2012.

I've converted most of the query over to SQL Server but am having a hard time translating over these queries to SQL Server logic.

sys_connect_by_path example:

SELECT sys_connect_by_path(field1, '/') || '/' || field2 || '/' path
  FROM data
 WHERE connect_by_isleaf = 1 connect BY prior field2 = field1 start
 WITH field1 NOT IN (SELECT field2 FROM data)

I know that I need to use a CTE for this example (possibly recursive) but I can't seem to get my logic correct

regexp example:

select field1, field2, regexp_substr(path, '[^/]+', 1, column_value) elem
  from grouped,
table(cast(multiset(select level from dual
connect by level <= regexp_count(path,'/')) as sys.odciNumberList))
where regexp_substr(path, '[^/]+', 1, column_value) is not null

listagg example:

select field1, field2,
       dense_rank() over (order by listagg(elem,',') within group (order by elem)) grp
  from split
group by field1, field2

From what I understand, listagg in SQL is done by the STUFF function. That seems simple enough to understand but in this case I'm not sure how I make it work

Could I get some pointers?

  • 1
    Are you using SSMA For Oracle? It's got a bunch of conversion logic and compatibility functions already implemented. microsoft.com/en-us/download/details.aspx?id=54258 Also newer versions of SQL might make this easier. – David Browne - Microsoft Sep 5 '18 at 14:56
  • I tried that but I do not have enough access to the database and I cannot get any additional access. – jdids Sep 5 '18 at 15:08
  • 1
    You can use the "Scripts" functionality in SSMA to paste and convert individual statements, or you can use a local Oracle instance you control to work on the schema and code migration. – David Browne - Microsoft Sep 5 '18 at 15:12
  • Thanks David. Didn't think about creating a local Oracle instance. I'm doing that now. – jdids Sep 5 '18 at 15:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.