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 )
where STATE_PROVINCE = 'FL'
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?
Oracle Riddle Blog 
Without checking the original post or SQL-documentation I try to make sense out of the query and first question:
Let’s go through the creation of the result set:
first a set of tupels (quantity,unit_price, order_id) is needed.
Those are then aggregated with the function sum(quantity*unit_price) over order_id.
To check if there is a need for distinction of orders.order_id or order_items.order_id we can put poth into the tupel. Then we have (quantity,unit_price, order_id1, order_id2)
A quick check will show us order_id1 is equal to order_id2 in every tupel.
This is a requirement as there is a join on order_id
So if they are equal, we can choose either.
And so can do any SQL-parser if this rule is implemented.
LikeLike
For the 2nd question, I’d exchange
where STATE_PROVINCE = ‘FL’
by
where TREAT(CUST_ADDRESS as CUST_ADDRESS_TYP).STATE_PROVINCE = ‘FL’
(sorry for not reading the post exactly and asking on twitter)
LikeLike