sql - How to execute CTE Queries in Oracle 11g -

i have oracle 11g database. when execute query containig with clause doesn't show result.

query:

with   seq (select to_char(level) p             dual connect level <= 10) select old_value, new_value   (select to_char(t1.p) old_value,                to_char(t2.p) new_value           (select row_number() over(order p) rn, p seq) t1,                (select row_number() over(order dbms_random.random) rn,                        p                    seq) t2          t1.rn = t2.rn); 

when execute query shows no records: 0 rows selected in 0.0078 seconds.

why result empty?

it works me on 11.2.0.2:

with seq (select to_char (level) p                dual              connect level <= 10) select old_value,        new_value   (select t1.p old_value,                t2.p new_value           (select row_number () on (order p) rn, p seq) t1,                (select row_number () on (order dbms_random.random) rn, p                   seq) t2          t1.rn = t2.rn);  old_value  new_value  ---------- ---------- 1          7          10         5          2          4          3          6          4          3          5          8          6          1          7          2          8          9          9          10    

n.b. although works to_char(t1.p) , to_char(t2.p) old/new value column definitions, to_chars totally redundant, since t1.p , t2.p strings (as defined in seq subquery). that's why removed them in above query.

---

eta: if you're going use subquery factoring (which oracle term cte), why not go whole hog , put subqueries in clause?

e.g.:

with seq (select to_char (level) p                dual              connect level <= 10),       t1 (select row_number () on (order p) rn,                     p                seq),       t2 (select row_number () on (order dbms_random.random) rn,                     p                seq) select t1.p old_value,        t2.p new_value   t1        inner join t2 on (t1.rn = t2.rn); 

a couple of things notice: a) removed unnecessary outer query , b) converted old style join ansi style join.