Re: PL/SQL statement help

Milton, do yourself a favor and sign up for OTN(http://otn.oracle.com) you got access to full oracle docs online.

joe

Milton C. Craighead, Jr. wrote:

> I'm having no luck resolving the following PL/SQL errors for a final
> exam class project. We do not have access to metalink nor do we have
> access to Oracle PL/SQL documentation. We are being taught from a
> PL/SQL text book that does not provide ora error message information
> or where to find them... Any and all assistance will be greatly
> appreciated.....
>
> 1) where am I going wrong????
>
> SQL> SELECT order#, customer#, address, city, state, zip,
> 2 orderdate, shipdate, shipstreet, shipcity, shipstate,
> 3 shipzip, item#, isbn, quantity
> 4 FROM customers NATURAL JOIN orders;
> shipzip, item#, isbn, quantity
> *
> ERROR at line 3:
> ORA-00904: "QUANTITY": invalid identifier
> 2) I'm trying to format a report where the output should look like the
> following:
> I'm able to format the item#, isbn, quanty, retail, and total line
> with out error but Im getting an error message in my veiw...
>
>
> Order#
> Customer#
> Customer Address
> Customer City, State, Zip
>
> Order Date
> Ship Date
> Ship Street
> Ship City, Ship State, Ship Zip
>
> ITEM# ISBN QUANTY RETAIL TOTAL LINE
> ITEM# ISBN QUANTY RETAIL TOTAL LINE
>
>
> SET SERVEROUTPUT ON
> SQL> set linesize 300
> SQL> set pagesize 20
> SQL> ttitle CENTER 'The Law Report' SKIP 2
> SQL> btitle '(Confidential)' SKIP 2
> SQL>
> SQL> column item# heading 'ITEM#' format a10 truncate
> SQL> column isbn heading 'ISBN' format a15 truncate
> SQL> column quantity heading 'QUANTITY' format a6 truncate
> SQL> column retail heading 'RETAIL' format 990.00
> SQL> column total line heading 'TOTAL|LINE' format 990.00
> SP2-0158: unknown COLUMN option "line"
> SQL>
> SQL> Drop view bookPub;
>
> View dropped.
>
> SQL> Create view bookPub
> 2 AS select count(b.title) as bookNum, b.cost, b.pubid
> 3 from books b natural join orderitems o
> 4 group by b.pubid, b.cost;
>
> View created.
>
> SQL>
> SQL>
> SQL>
> SQL>
> SQL> Drop view bookRev;
>
> View dropped.
>
> SQL> Create view bookRev
> 2 As select count(b.title) as bkNum, sum(b.retail - b.cost) as
> rev, o.customer#
> 3 from orderitems i natural join books b natural join orders o
> 4 group by o.customer#;
>
> View created.
>
> SQL>
> SQL>
> SQL>
> SQL> Drop view orderInfo;
>
> View dropped.
>
> SQL> Create view orderInfo
> 2 As
> 3 Select c.customer#, o.order#, c.lastname, c.firstname, c.address,
> 4 o.shipdate, o.shipstreet, o.shipcity, o.shipstate,
> 5 o.shipzip, i.item#, i.isbn, i.quantity
> 6 from customers c, orders o, orderitems i Where c.customer# =
> o.customer#(+)
> 7 and o.order# = i.order#;
>
> View created.
>
> SQL>
> SQL> CLEAR BREAK
> breaks cleared
> SQL> CLEAR COLUMN
> columns cleared
> SQL>
> SQL> SELECT order#, customer#, address, city, state, zip,
> 2 orderdate, shipdate, shipstreet, shipcity, shipstate,
> 3 shipzip, item#, isbn, quantity
> 4 FROM customers NATURAL JOIN orders;
> shipzip, item#, isbn, quantity
> *
> ERROR at line 3:
> ORA-00904: "QUANTITY": invalid identifier
>
>
> SQL>
> SQL> SELECT item#, isbn, quantity, retail, sum(retail*quantity) "Total
> Line"
> 2 FROM customers NATURAL JOIN orders NATURAL JOIN orderitems
> 3 NATURAL JOIN books
> 4 GROUP BY item#, isbn, quantity, retail;
> 3) last but not least I can not figure out how to create this
> statement at all:
>
> Deletes are not allowed, but you will be allowed to de-activate
> orders. You will need to add a column to the orders table to
> maintain the status, and then only allow updates of that colum through
> a procedure.
>
> Again thanks for any and all assiatance....
>
> Regards,
> Milton C. Craighead, Jr.
Received on Thu Jul 24 2003 - 11:04:06 CDT