SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

convert query from MSSQL to Oracle

MoazzamMar 24 2011 — edited Mar 24 2011

I am using Oracle 10.2 and want to convert following SQLServer query to oracle:

WITH Factor (Code, StartDate, EndDate, Rate, Ranks, Factors) AS
(
	SELECT Code, StartDate, EndDate, Rate, Ranks, CONVERT(FLOAT, 1.0)
	FROM GSplitDateRangeRank
	WHERE Ranks = 1
	UNION ALL
	SELECT S.Code, S.StartDate, S.EndDate, S.Rate, S.Ranks, CASE WHEN S.Rate > 0 THEN F.Factors / S.Rate ELSE F.Factors END
	FROM GSplitDateRangeRank S
	JOIN Factor F
	ON S.Code = F.Code
	AND S.Ranks = F.Ranks + 1
)
SELECT * FROM Factor order by code

Following is the query converted in Oracle:

with Factor AS
(
	SELECT Code, StartDate, EndDate, Rate, Ranks, 1.0 Factors
	FROM GSplitDateRangeRank
	)
select Code, StartDate, EndDate, Rate, Ranks, CASE WHEN Rate > 0 THEN Factors / Rate ELSE Factors END Factors
from Factor 
start with ranks = 1 
connect by code = code
            and  ranks = prior ranks +1;

But the rows generated by Oracle query are lot more than those in MSSQL. Can any body kindly help me.

This post has been answered by Frank Kulash on Mar 24 2011

Jump to Answer

Aketi Jyuuzou

I like recursive with clause B-)

But... I suppose that this case needs model clause :D
Because Factors needs recursive calc.

select Code, StartDate, EndDate, Rate, Ranks,
CASE WHEN Level = 1 then 1.0
     when Rate > 0 THEN Factors / Rate ELSE Factors END Factors
from Factor 
start with ranks = 1
connect by code = prior code
      and  ranks = prior ranks +1;

My SQL articles of OTN-Japan ;-)
http://www.oracle.com/technetwork/jp/articles/otnj-sql-image3-1-323602-ja.html

1 person found this helpful

Frank Kulash

Answer

Hi,

Here's how you adapted the recursive condition on ranks:
SQL Server:

Moazzam wrote:

	AND S.Ranks = F.Ranks + 1

Oracle:

and  ranks = prior ranks +1;

That looks right.

Now here's how you adapted the condition on code:

	ON S.Code = F.Code

Oracle:

and  ranks = prior ranks +1;

"code = code" will always be true (unless code is NULL). If it's correct to change
"F.ranks" to "PRIOR ranks", then it's correct to change
"F.code" to "PRIOR code" also:

CONNECT BY  code  = PRIOR code

It's too bad you're not using Oracle 11.2, which has recursive sub-queries. The SQL Server code might run without any changes.

Marked as Answer by Moazzam · Sep 27 2020

Moazzam

Thanks Frank for the help.

Actually we are to deploy this stored procedure on client side that are using different versions of Oracle from 9i to 11g, therefore, i cannot use recursive with clause available in 11g.

1 - 3

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 21 2011

Added on Mar 24 2011

3 comments

578 views

SQL & PL/SQL

convert query from MSSQL to Oracle

Comments

Post Details