Month: May 2015

Why do we not need an alias?

Here’s a question from OTN. I’m curious what you have to say. (Please do not check the answer to OTN.)

select order_id, sum(quantity*unit_price)
from order_items
having sum(quantity*unit_price) > ALL
(select sum(quantity*unit_price)
from customers
join orders using (customer_id)
join order_items using (order_id )
group by order_id)
group by order_id;

The question is: Why do we not need an alias for order_id in the “group by order_id” clause in the sub query, although the column appears in two tables?
Thus, to make it a bit more difficult, I have built a small additional hurdle. STATE_PROVINCE is in the OE schema for version 12 (for other versions I have not tested) component of a type variable. The query is not running as shown here. How should we modify the query so that it will work?