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
I want to convert this to Oracle 10g (must 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.
CONNECT BY