开发者

Convert SQL Server recursive WITH clause to Oracle 10g

I have problem with this query

DECLARE @INPUT INT
SET     @INPUT = 12345

;
WITH ABCD(SEQ, X, Y)
AS
(
    SELECT  1, 
            @INPUT % 10,
            @INPUT / 10

    UNION ALL

    SELECT  SEQ + 1,
            Y % 10,
            Y / 10
    FROM    ABCD
    WHERE   X > 0 OR Y > 0
)
SELECT *
FROM   ABCD
ORDER BY SEQ

this query will produce something like this

Convert SQL Server recursive WITH clause to Oracle 10g

I want to convert this to Oracle 10g (must 开发者_C百科valid for 10g)

Thank you


I'm cheating a bit to compute X by relying on string parsing rather than the mathematical operations. But something like this appears to work

EDIT: Forgot about the 6th row. I also removed the character string cheat

with t as (
  select 12345 col1 from dual
)
select level seq, 
       trunc((col1 - 
              power(10,level) * trunc(col1/(power(10,level)))) / 
             power(10,level-1)) x,
       trunc(col1/(power(10,level))) y 
  from t
 connect by level <= length(col1)+1
/

SQL> ed
Wrote file afiedt.buf

  1  with t as (
  2    select 12345 col1 from dual
  3  )
  4  select level seq,
  5         trunc((col1 -
  6                power(10,level) * trunc(col1/(power(10,level)))) /
  7               power(10,level-1)) x,
  8         trunc(col1/(power(10,level))) y
  9    from t
 10*  connect by level <= length(col1)+1
SQL> /

       SEQ          X          Y
---------- ---------- ----------
         1          5       1234
         2          4        123
         3          3         12
         4          2          1
         5          1          0
         6          0          0

6 rows selected.
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜