[I think i figured out the problem while creating the test for this post. My requests is for comments on this subject.]
I have a VIEW that SELECTs a COLUMN, and displays a value based on the outcome of a CASE statement, that refers to a COLUMN in a WITH TABLE.
If any COLUMNs other than CASEd COLUMN exists, and an aggregate is SELECTed FROM the VIEW that does not refer to the CASEd COLUMN, Oracle generates an ORA-32035 error (i'm guessing this is because Oracle skips the CASE (because it is only display, and not relevant to the aggregate FUNCTION)). If the CASE is the only COLUMN, it uses it, and no error is generated.
Test done in 10g XE
SQL> -- Try it with only a CASEd COLUMN
SQL> CREATE OR REPLACE VIEW A AS
2 WITH B AS (SELECT Dummy FROM Dual)
3 SELECT
4 CASE WHEN (SELECT 1 FROM B) > 0 THEN 1 ELSE 2 END A
5 FROM
6 Dual;
View created.
SQL>
SQL> SELECT * FROM A;
A
----------
1
SQL> SELECT COUNT(*) FROM A;
COUNT(*)
----------
1
SQL>
SQL> -- Add another COLUMN, and the COUNT(*) fails.
SQL> CREATE OR REPLACE VIEW A AS
2 WITH B AS (SELECT Dummy FROM Dual)
3 SELECT
4 Dummy,
5 CASE WHEN (SELECT 1 FROM B) > 0 THEN 1 ELSE 2 END A
6 FROM
7 Dual;
View created.
SQL>
SQL> SELECT * FROM A;
D A
- ----------
X 1
SQL> SELECT COUNT(*) FROM A;
SELECT COUNT(*) FROM A
*
ERROR at line 1:
ORA-32035: unreferenced query name defined in WITH clause
SQL>
SQL> -- Forcing it to use the CASEd COLUMN works.
SQL> SELECT COUNT(A) FROM A;
COUNT(A)
----------
1
SQL> SELECT COUNT(*) FROM (SELECT * FROM A);
COUNT(*)
----------
1
SQL>
SQL> EXPLAIN PLAN FOR SELECT COUNT(A) FROM A;
Explained.
SQL> @explain
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
Plan hash value: 3429071782
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00
| 1 | SORT AGGREGATE | | 1 | 3 | |
| 2 | VIEW | A | 1 | 3 | 2 (0)| 00:00
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00
--------------------------------------------------------------------
10 rows selected.
SQL> EXPLAIN PLAN FOR SELECT COUNT(*) FROM (SELECT * FROM A);
Explained.
SQL> @explain
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
Plan hash value: 4006255476
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | VIEW | A | 1 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DUAL | 1 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------
10 rows selected.
SQL> DROP VIEW A;
View dropped.