what is wrong with this oracle query?

Question

SELECT *
  FROM (SELECT ROWNUM rnum,
               query.*
         FROM  (WITH myQuery AS(
                     SELECT column_b
                       FROM table_a a
                     WHERE a.column_a = 1234)
         SELECT b.column_e AS some_column
           FROM table_b b,
                table_c c,
                table_a a
      LEFT JOIN table_d d ON c.column_c = d.column_d
           JOIN myQuery mq ON a.column_b = mq.column_b
          WHERE b.column_b = a.column_b) query)
 WHERE rnum > 0

Answer 1

Don't mix ANSI-88 and ANSI-92 JOIN syntax, pick one or the other. Here's your query using ANSI-92 syntax:

WITH myQuery AS (
  SELECT column_b
    FROM table_a a
   WHERE a.column_a = 1234)
SELECT x.*
  FROM (SELECT b.column_e AS some_column,
               ROWNUM 'rnum'
          FROM table_b b
          JOIN TABLE_A a ON a.column_b = b.column_b
          JOIN myQuery mq ON mq.column_b = a.column_b
          JOIN table_c c ON c.? = ?? --need join criteria here
     LEFT JOIN table_d d ON c.column_c = d.column_d) x
 WHERE x.rnum > 0

Your example lacks what TABLE_C joins on to - hence the ? and ??

I didn't know that WITH clauses can be defined in subqueries - I was sure I'd encountered an error in the past when attempting it in 10g.