3 Replies Latest reply: Mar 24, 2011 7:20 AM by Moazzam RSS

    convert query from MSSQL to Oracle

    Moazzam
      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
          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
            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
              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.