The database is running on Oracle Database 11g using SQL *Plus 11.2. Are aggregate methods not allowed in a WITH clause or is WITH doing something magical? This code tells me "most_expensive" is an invalid identifier. Yet, a sub-query works with no issue.
WITH most_expensive AS (SELECT MAX (enrollment_cost) FROM Enrollments)
SELECT e.member_id
FROM Enrollments e
WHERE e.enrollment_cost = most_expensive;
Query factoring (with clauses) allows you to define temporary table aliases.
In your example most_expensive is going to reference a table object containing a single row with a single column. You can use it anywhere in the query where you can use a table.
Now, if you create a table called t1 (with create table statment), give it one column and insert 1 row, you still won't be able to do "WHERE x = t1".
In other words, a subquery is not always the same as a table, and WITH clauses gives you something that behaves like tables, not like subqueries.
The following works though:
WITH most_expensive AS (SELECT MAX (enrollment_cost) FROM Enrollments)
SELECT member_id
FROM Enrollments e
WHERE e.enrollment_cost = (select * from most_expensive);
I don't see any benefit of using sub-query factoring(WITH clause) here. The query could simply be written as:
SELECT member_id
FROM Enrollments e
WHERE e.enrollment_cost =
(SELECT MAX (enrollment_cost) FROM Enrollments
);
Compare the explain plans:
Without sub-query factoring:
SQL> set autot on explain
SQL> SELECT empno FROM emp e WHERE e.sal =
2 (SELECT MAX (sal) FROM emp
3 );
EMPNO
----------
7839
Execution Plan
----------------------------------------------------------
Plan hash value: 1876299339
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 8 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | EMP | 1 | 8 | 4 (0)| 00:00:01 |
| 2 | SORT AGGREGATE | | 1 | 4 | | |
| 3 | TABLE ACCESS FULL| EMP | 14 | 56 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."SAL"= (SELECT MAX("SAL") FROM "EMP" "EMP"))
With sub-query factoring:
SQL> WITH max_sal AS
2 ( SELECT MAX (sal) sal FROM emp
3 )
4 SELECT empno FROM emp e WHERE e.sal =
5 (SELECT sal FROM max_sal
6 );
EMPNO
----------
7839
Execution Plan
----------------------------------------------------------
Plan hash value: 73843676
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 8 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | EMP | 1 | 8 | 4 (0)| 00:00:01 |
| 2 | VIEW | | 1 | 13 | 4 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 4 | | |
| 4 | TABLE ACCESS FULL| EMP | 14 | 56 | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."SAL"= (SELECT "SAL" FROM (SELECT MAX("SAL") "SAL"
FROM "EMP" "EMP") "MAX_SAL"))
See the filter applied, all you are doing is making it nested query and going one level deep without actually adding any benefit.
Your factored query(table) is named most_expensive but you use it as column name. In this case you should rather use keep first.
SELECT max(member_id) KEEP (DENSE_RANK FIRST ORDER BY enrollment_cost desc nulls last) "Best"
FROM Enrollments e
WHERE e.enrollment_cost = most_expensive;
most_expensivein your query - only a sub-query. But you refer to a column of that name in yourwhereclause