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 )
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?

Advertisements

2 comments

  1. 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.

    Like

  2. 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)

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s