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.
--
Joseph S Testa
Chief Technology Officer Data Management Consulting
p: 614-791-9000
f: 614-791-9001
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
