Assign a Name to the Sub queries in Oracle

Question

I have 2 sub queries which are repeated and are very long so I want to give it a name and then refer that name in the query as mentioned in https://stackoverflow.com/a/3058938/6700081.

I have a Customers table with ID, Name, OrderID

I have the Orders table with ID (this is the order ID so it is also the Foreign key), Price, Order_Date

This is my original query which is working fine:

SELECT CUSTOMERS.name, ORDERS.price FROM 
CUSTOMERS INNER JOIN ORDERS
ON (CUSTOMERS.ORDER_ID = ORDERS.ID)
WHERE ORDERS.PRICE = (SELECT MAX(ORDERS.PRICE) from ORDERS where ORDERS.ORDER_DATE <= (SELECT ADD_MONTHS((SELECT MIN(ORDER_DATE) FROM ORDERS), 12*10) from ORDERS WHERE ROWNUM = 1))
AND ORDERS.ORDER_DATE <= (SELECT ADD_MONTHS((SELECT MIN(ORDER_DATE) FROM ORDERS), 12*10) from ORDERS WHERE ROWNUM = 1);

I tried to change it to a named query as below:

WITH MAX_ORDER_DATE as (SELECT ADD_MONTHS((SELECT MIN(ORDER_DATE) FROM ORDERS), 12*10) from ORDERS WHERE ROWNUM = 1), 
WITH MAX_ORDER_PRICE as (SELECT MAX(ORDERS.PRICE) from ORDERS where ORDERS.ORDER_DATE <= (MAX_ORDER_DATE)) 
SELECT CUSTOMERS.name, ORDERS.price FROM 
CUSTOMERS INNER JOIN ORDERS
ON (CUSTOMERS.ORDER_ID = ORDERS.ID)
WHERE ORDERS.PRICE = (MAX_ORDER_PRICE)
AND ORDERS.ORDER_DATE <= (MAX_ORDER_DATE);

But I get an error related to invalid table name. What is wrong with this query?

Remove the second `WITH` and it should work. `WITH` should only be specified once, at the start of the CTE defintions. — Tim Biegeleisen, May 22 '20 at 13:33
I stil get an error that MAX_ORDER_DATE is an invalid identifier — firstpostcommenter, May 22 '20 at 13:36
Yes, you had other issues, I have posted a correction below. — Tim Biegeleisen, May 22 '20 at 13:42

score 1 · Accepted Answer · answered May 22 '20 at 13:41

Your corrected query:

WITH MAX_ORDER_DATE AS (
    SELECT ADD_MONTHS((SELECT MIN(ORDER_DATE) FROM ORDERS), 12*10) AS max_date
    FROM ORDERS
    WHERE ROWNUM = 1
), 
MAX_ORDER_PRICE AS (
    SELECT MAX(ORDERS.PRICE) AS max_price
    FROM ORDERS
    WHERE ORDERS.ORDER_DATE <= (SELECT max_date FROM MAX_ORDER_DATE)
) 

SELECT c.name, o.price
FROM CUSTOMERS c
INNER JOIN ORDERS o
    ON c.ORDER_ID = o.ID
WHERE
    o.PRICE = (SELECT max_price FROM MAX_ORDER_PRICE) AND
    o.ORDER_DATE <= (SELECT max_date FROM MAX_ORDER_DATE);

The main issues I noticed with your syntax were that you repeated WITH for each common table expression, when you only need to state it once at the beginning of the definitions. Also, if you want to use the single values you define in the two CTEs, you should use a subquery against those CTEs. Finally, I added aliases to the columns you select in the CTEs.

Assign a Name to the Sub queries in Oracle

1 Answers1