Home » SQL & PL/SQL » SQL & PL/SQL » Converting SQL from MSSQL to ORACLE
Converting SQL from MSSQL to ORACLE [message #272590] Fri, 05 October 2007 11:48 Go to next message
parthav
Messages: 5
Registered: October 2007
Location: Santa Clara, CA
Junior Member
Can anyone tell me how i can convert this to oracle?

Thanks in Advance.
--------------------------------------------------------------------
DECLARE @spvsr TABLE (empid int, spvsrid int, levelcount int);
with spvsr (empid, spvsrid, levelcount) as (
SELECT s.employeeid,s.supervisoremployeeid, 0
FROM supervisor s
union all
SELECT
s2.empid, s.supervisoremployeeid, s2.levelcount + 1
FROM supervisor s, spvsr s2
WHERE s.employeeid= s2.spvsrid and levelcount < 5
)
--------------------------------------------------------------------
Re: Converting SQL from MSSQL to ORACLE [message #272592 is a reply to message #272590] Fri, 05 October 2007 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you explain what it is and what it does?

Regards
Michel
Re: Converting SQL from MSSQL to ORACLE [message #272604 is a reply to message #272592] Fri, 05 October 2007 12:53 Go to previous messageGo to next message
parthav
Messages: 5
Registered: October 2007
Location: Santa Clara, CA
Junior Member
This is a recursive sql statement to find the relationship between EMPLOYEEID and SUPERVISOREMPID in supervisor table. We are interested up to 5 levels of relationship.

This is part of migration code; our application currently allows the relationship as follows

ID EMPLOYEEID SUPERVISOREMPID Comments
1 1 2
2 2 3
3 3 1 Currently we are allowing this loop back
and we wanted to control in future
4 4 5
5 5 4 This should also be avoided, but this may
be the reason 5 got de-promoted and 4 got
promoted

Supervisor table has columns as follows
ID
EMPLOYEEID
SUPERVISOREMPID
STARTDATE
ENDDATE
CREATED
CREATEDBY
MODIFIED
MODIFIEDBY
Re: Converting SQL from MSSQL to ORACLE [message #272608 is a reply to message #272604] Fri, 05 October 2007 13:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a clue of what you can do, using the standard EMP table:
SQL> select empno, mgr, level
  2  from emp
  3  connect by prior empno = mgr
  4  start with mgr is null
  5  /
     EMPNO        MGR      LEVEL
---------- ---------- ----------
      7839                     1
      7566       7839          2
      7788       7566          3
      7876       7788          4
      7902       7566          3
      7369       7902          4
      7698       7839          2
      7499       7698          3
      7521       7698          3
      7654       7698          3
      7844       7698          3
      7900       7698          3
      7782       7839          2
      7934       7782          3

14 rows selected.

Regards
Michel
Re: Converting SQL from MSSQL to ORACLE [message #272621 is a reply to message #272608] Fri, 05 October 2007 13:47 Go to previous messageGo to next message
parthav
Messages: 5
Registered: October 2007
Location: Santa Clara, CA
Junior Member
Thanks for your effort and time.
I am getting ORA- 01436: CONNECT BY loop in user data
Re: Converting SQL from MSSQL to ORACLE [message #272626 is a reply to message #272621] Fri, 05 October 2007 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is weird you have a cycle in a employee/supervisor chain.
Anyway, you can use NOCYCLE option.
I forgot the condition in level in the previous query.
Now this give:
select empno, mgr, level
from emp
where level <= 5
connect by nocycle prior empno = mgr
start with mgr is null
/

Regards
Michel
Re: Converting SQL from MSSQL to ORACLE [message #272630 is a reply to message #272626] Fri, 05 October 2007 14:34 Go to previous message
parthav
Messages: 5
Registered: October 2007
Location: Santa Clara, CA
Junior Member
Thank you very much Michel. Your help is highly appreciated.
Previous Topic: can we call any procedure from select statement
Next Topic: SELECT email address and only display username Array? Delimiter?
Goto Forum:
  


Current Time: Fri Dec 09 23:34:38 CST 2016

Total time taken to generate the page: 0.08970 seconds