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.
