0

I am unable to convert this SQL Server code to Oracle code. I am not SQL expert, can anyone help in converting this code to Oracle compatible.

 Declare @lvl as int
 Declare @rows as int
 DECLARE @foo as Table(
     KV_MANAGERNR int,
     KV_PERSONNR varchar(10),
     ord int,
     lvl int)

INSERT @foo (KV_MANAGERNR, KV_PERSONNR, ord, lvl)
select KV_MANAGERNR, KV_PERSONNR, row_number() over(order by KV_PERSONNR), 0
    from PERSONSMANAGER where KV_MANAGERNR='127723'

set @rows=@@ROWCOUNT
set @lvl=0

--Do recursion
WHILE @rows > 0
BEGIN
    set @lvl = @lvl + 1

    INSERT @foo (KV_MANAGERNR, KV_PERSONNR, ord, lvl)
    SELECT DISTINCT b.KV_MANAGERNR, b.KV_PERSONNR, row_number() over(order 
    by b.KV_PERSONNR), @lvl
    FROM PERSONSMANAGER b
     inner join @foo f on b.KV_MANAGERNR = f.KV_PERSONNR
     --might be multiple paths to this recursion so eliminate duplicates
     left join @foo dup on dup.KV_PERSONNR = b.KV_PERSONNR
     WHERE f.lvl = @lvl-1 and dup.KV_PERSONNR is null

    set @rows=@@ROWCOUNT 
END

SELECT DISTINCT KV_PERSONNR from @foo order by KV_PERSONNR
Manish Kapasiya
  • 39
  • 4
  • What error(s) do you get? – SchmitzIT Dec 06 '17 at 07:38
  • In Line 1 & 2 syntax error while declaring these variables. and many more errors – Manish Kapasiya Dec 06 '17 at 09:39
  • So have you attempted to try and find out how to declare variables in Oracle? It takes 1 Google search to find it uses a different syntax than SQL Server. My suggestion is that you start solving errors 1 by 1. You will likely run into the next error. If you get stuck with that, you can always post a new question with a specific request. Right now it appears as if you simply want someone to translate the code for you. SO is not meant for that. We love to help you out with specific problems, but reasonably expect to see some effort from your side. – SchmitzIT Dec 06 '17 at 09:48
  • Please don't expect us to write the entire code for saving your time. We like to help when you have shown some effort in writing it yourself. It's not required that you be an SQL expert to make an attempt – Kaushik Nayak Dec 06 '17 at 16:27
  • Thanks Schmitz and Kaushik. I have tried to convert the code. Please check whether I can further improve it or are there any bugs present. Because this code is giving me results. – Manish Kapasiya Dec 07 '17 at 09:26

1 Answers1

0
DECLARE  
    v_lvl Number:=0;  
    v_rows number:=1;  
BEGIN  
    INSERT INTO nt_list_0 (KV_MANAGERNR, KV_PERSONNR, ord, lvl)  
        select KV_MANAGERNR, KV_PERSONNR, row_number() over(order by 
        KV_PERSONNR), 0  from PERSONSMANAGER where KV_MANAGERNR='100047';  

    WHILE v_rows>0  
    LOOP  
        v_lvl := v_lvl +1;  

        INSERT INTO nt_list_0 (KV_MANAGERNR, KV_PERSONNR, ord, lvl)  
        SELECT DISTINCT b.KV_MANAGERNR, b.KV_PERSONNR, row_number() 
          over(order by b.KV_PERSONNR), v_lvl  
        FROM PERSONSMANAGER b  
        inner join nt_list_0 f on b.KV_MANAGERNR = f.KV_PERSONNR  
        left join nt_list_0 dup on dup.KV_PERSONNR = b.KV_PERSONNR  
        WHERE f.lvl = v_lvl-1 and dup.KV_PERSONNR is null;   

        v_rows := sql%rowcount;  
    END LOOP;  
END;
Manish Kapasiya
  • 39
  • 4