This discussion is archived
3 Replies Latest reply: Mar 24, 2011 5:20 AM by Moazzam RSS

convert query from MSSQL to Oracle

Moazzam Pro
Currently Being Moderated
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.
  • 1. Re: convert query from MSSQL to Oracle
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    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
  • 2. Re: convert query from MSSQL to Oracle
    Frank Kulash Guru
    Currently Being Moderated
    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
    "<b>F.</b>ranks" to "<b>PRIOR</B> ranks", then it's correct to change
    "<b>F.</b>code" to "<b>PRIOR</B> 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.
  • 3. Re: convert query from MSSQL to Oracle
    Moazzam Pro
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points