conversion from sql server to oracle [message #35926] |
Wed, 24 October 2001 08:10 |
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 |
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 !!
----------------------------------------------------------------------
|
|
|