Oracle SQL invalid identifier error in nested WITH subquery
<p>Below you will find three sample tables and data along with a query. This example might seem contrived, but it is part of much larger (nearly 1500 lines) SQL query. The original query works great, but I've run into a problem while adding some new functionality. </p>

<pre><code>CREATE TABLE rule_table (
    id_rule_table NUMBER (10),
    name VARCHAR2 (24),
    goal NUMBER (10),
    amount NUMBER (10)
);

INSERT INTO rule_table (id_rule_table, name, goal, amount) VALUES(1, 'lorem', 2, 3);
INSERT INTO rule_table (id_rule_table, name, goal, amount) VALUES(2, 'ipsum', 3, 3);
INSERT INTO rule_table (id_rule_table, name, goal, amount) VALUES(3, 'dolor', 4, 3);

CREATE TABLE content_table (
    id_content_table NUMBER (10),
    name VARCHAR2 (24),
    show_flag NUMBER (10)
);

INSERT INTO content_table (id_content_table, name, show_flag) VALUES(1, 'lorem', 0);
INSERT INTO content_table (id_content_table, name, show_flag) VALUES(2, 'ipsum', 1);
INSERT INTO content_table (id_content_table, name, show_flag) VALUES(3, 'dolor', 1);

CREATE TABLE module_table (
    id_module_table NUMBER (10),
    id_content_table NUMBER (10),
    name VARCHAR2 (24),
    amount NUMBER (10)
);

INSERT INTO module_table (id_module_table, id_content_table, name, amount) VALUES(1, 2, 'lorem', 10);
INSERT INTO module_table (id_module_table, id_content_table, name, amount) VALUES(2, 2, 'ipsum', 11);
INSERT INTO module_table (id_module_table, id_content_table, name, amount) VALUES(3, 2, 'dolor', 12);

SELECT RULE.id_rule_table
FROM rule_table RULE
WHERE (
    CASE
    WHEN RULE.goal &lt;= (

            WITH contentTbl (id_content_table)
            AS (
                SELECT id_content_table
                FROM content_table
                WHERE show_flag = 1
            ),

            modulesTbl (id_content_table, id_module_table)
            AS (
                SELECT C.id_content_table, M.id_module_table
                FROM contentTbl C
                JOIN module_table M ON M.id_content_table = C.id_content_table
                WHERE 4 &lt; M.amount - RULE.amount
            )

            SELECT SUM(M.id_module_table)
            FROM contentTbl C
            JOIN modulesTbl M ON C.id_content_table = M.id_content_table
    )
    THEN 1
    ELSE 0
    END
) = 1;

DROP TABLE rule_table;
DROP TABLE content_table;
DROP TABLE module_table;
</code></pre>

<p>If you try this you will receive the error ORA-00904: "RULE"."AMOUNT": invalid identifier. The problem lies with the line "WHERE 4 &lt; M.amount - RULE.amount".</p>

<p>If you replace RULE.amount, in that line, with some number (e.g., WHERE 4 &lt; M.amount - 3) then the query will run just fine. </p>

<p>As mentioned above, this is a snippet test case from a much larger query, so the structure of the query can't be (or hopefully doesn't need to be) changed too much. That is, ideally I'm looking for a solution that will allow me to use RULE.amount in the sub-query without changing anything other that the SQL inside of the "WHEN RULE.goal &lt;= ()" block.</p>

<p>I'm trying to run this on Oracle 11g.</p>

<p>One last thing, I tried searching google and stackoverflow for solutions, but I couldn't figure out the correct terminology to describe my issue. The closest thing seemed to be nested correlated subquery, but that doesn't seem to be exactly right.</p>