RE: PL/SQL statement help

[Shamita Singh]

For the error: SP2-0158: unknown COLUMN option "line", "column total line" must be one word.. no spaces allowed. Can be total_line or just total.

 

Shamita

 

Shamita
"Nelson, Allan" <[EMAIL PROTECTED]> wrote:

quantity is neither a column name or a valid column alias.  That's what your error is telling you. 

 

Allan

-----Original Message-----
From: Milton C. Craighead, Jr. [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 24, 2003 11:39 AM
To: Multiple recipients of list ORACLE-L
Subject: PL/SQL statement help

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.

______________________________________________________________________________
This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216]

Shamita

---

Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software