Home » SQL & PL/SQL » SQL & PL/SQL » conversion from sql server to oracle
conversion from sql server to oracle [message #35926] Wed, 24 October 2001 08:10 Go to next message
ash
Messages: 43
Registered: February 2001
Member
I have following stored procedure in SQL Server.

--------------------------
Select e.id, t.testid, t.datetested, t.testresult into #tmp1
from emp e inner join test t on e.id = t.empid
order by e.id asc, t.datetested asc

select min(datetested) as Testdate, id into #tmp2
from #tmp1 group by id

select distinct #tmp1.* into #tmp3
from #tmp1 inner join #tmp2 on #tmp1.DateTested = #tmp2.TestDate
and #tmp1.id = #tmp2.id

select #tmp3.*,
(select count(t1.testid) from #tmp3 t1 where t1.testresult >= 0
and t1.testresult < 10 and t1.testid = #tmp3.testid) as level1,
(select count(t2.testid) from #tmp3 t2 where t2.testresult >= 10
and t2.testresult < 15 and t2.testid = #tmp3.testid) as level2
into #tmp4
from #tmp3

select @sql = 'Select count(distinct clientid) as numclients, count(testid) as numtests,
sum(level1) as level1,
sum(level2) as level2
from #tmp4'

exec (@sql)
----------------------

I want to convert SQL server stored procedure into oracle stored procedure and return the last statement as an output into a ref cursor. But before that I want to do check all above conditions. Is it possible to write a query which will return my output as a cursor and merge all above conitions ? Or is there any other way out ?

Here,
1. inner join in SQL server is same as equi join in Oracle.
2. you can create a virtual table and access it in sql server.

If there is any question regarding SQL server syntax please ask me.

Your help is appreciated.

Thanks in advance.

----------------------------------------------------------------------
Re: conversion from sql server to oracle [message #35941 is a reply to message #35926] Thu, 25 October 2001 03:32 Go to previous message
Satish Shrikhande
Messages: 167
Registered: October 2001
Senior Member
hi Ash ,
if you wanna to take the records fetched by the following query
select @sql = 'Select count(distinct clientid) as numclients, count(testid) as numtests,
sum(level1) as level1,
sum(level2) as level2
from #tmp4'

You need define a cursor in declaration part of procedure and then open the cursor and fetch it one by one .
cursor c1 is
Select count(distinct clientid) numclients, count(testid) numtests,
sum(level1) level1,
sum(level2) level2
from tmp4

for z in c1 loop
m_level1:=z.level1;
end loop;

right now i am doing migration of Database from oracle 8.1.6 to Sql server 2000 .So little bit busy , if you have any queries send me an email satish_isi@rediffmail.com and definately i will try to solve your probs .

have Fun !!

----------------------------------------------------------------------
Previous Topic: What is it??
Next Topic: converting a pl/sql rocedure into an exe file or any OS file
Goto Forum:
  


Current Time: Thu Apr 02 02:22:13 CDT 2020