JOIN alias does not exist and improperly ended

Question

As discussed in this thread, the subquery in the from clause is not necessary and causes a Cartesian product, but new questions arise.

SELECT customer.customer_name
       ,orders.order_date
       ,order_line.num_ordered
       ,order_line.quoted_price
       ,part.descript
       ,amt_billed
FROM   (SELECT order_line.num_ordered*part.price AS amt_billed
          FROM order_line
          JOIN part
            ON order_line.part_num = part.part_num
          ) billed
           ,customer
      JOIN orders
        ON customer.customer_num = orders.customer_num
      JOIN order_line
        ON orders.order_num = order_line.order_num
      JOIN part
        ON order_line.part_num = part.part_num;

Let us say I was determined to use the subquery in the from clause. If you read the comments, @JorgeCampos suggested I use an AS to establish an alias and to then write a join between the subquery and an existing table.

When I place an AS in between the subquery and the alias billed, I get a ORA-00933: SQL command not properly ended. Removing the AS and running it returns no error but a Cartesian product.

Further more, any join I try to make using the billed alias returns an ORA-00942: table or view does not exist.

For reference sake, here is the ERD for the entire database:

In conclusion, I want to know how this would be written if I was determined to keep the subquery in the from clause.

I am using Oracle 11g pl/sql.

Update

This:

SELECT customer.customer_name
       ,orders.order_date
       ,order_line.num_ordered
       ,order_line.quoted_price
       ,part.descript
       ,amt_billed
FROM (SELECT order_line.num_ordered*part.price AS amt_billed, orders.order_num
        FROM order_line
        JOIN part
          ON order_line.part_num = part.part_num
           ) AS billed
    JOIN customer
      ON customer.customer_num = orders.customer_num
    JOIN billed
      ON orders.order_num = order_line.order_num
    JOIN orders
      ON customer.customer_num = orders.customer_num
    JOIN order_line
      ON orders.order_num = order_line.order_num
    JOIN part
      ON order_line.part_num = part.part_num;

Returns this:

       ) AS billed
         *
ERROR at line 11:
ORA-00933: SQL command not properly ended

Removing AS at line 11 returns this:

JOIN billed
     *
ERROR at line 14:
ORA-00942: table or view does not exist

Update

Thanks to @GordonLinoff, I present to you the final code!

SELECT customer.customer_name
       ,orders.order_date
       ,order_line.num_ordered
       ,order_line.quoted_price
       ,amt_billed
       ,part.descript
  FROM customer
    JOIN orders
      ON customer.customer_num = orders.customer_num
    JOIN order_line
      ON orders.order_num = order_line.order_num
    JOIN part
      ON order_line.part_num = part.part_num
    JOIN (SELECT DISTINCT order_line.part_num
                          ,order_line.num_ordered*part.price AS amt_billed
            FROM order_line
            JOIN part
              ON order_line.part_num = part.part_num
        ) billed
      ON billed.part_num = order_line.part_num
ORDER BY customer.customer_name, orders.order_date;

I did as he said and removed the redundant join to customer. I also had to swap out what the subselect was joined on. If I stuck with the num_ordered, rows would repeat their selves whenever one part was ordered.

Answer 1

Oracle doesn't support as for table aliases. So:

FROM (SELECT order_line.num_ordered*part.price AS amt_billed, orders.order_num
      FROM order_line JOIN
           part
           ON order_line.part_num = part.part_num
      ) billed JOIN
--------^
      Customer
      . . .

EDIT:

You don't need to repeat billed. You want something like this:

FROM customer JOIN
     orders
     ON customer.customer_num = orders.customer_num JOIN
     order_line
     ON orders.order_num = order_line.order_num JOIN
     part
     ON order_line.part_num = part.part_num JOIN
     (SELECT order_line.order_num, order_line.num_ordered*part.price AS amt_billed
      FROM order_line JOIN
           part
           ON order_line.part_num = part.part_num
     ) billed
     ON billed.order_num = order.order_num;