RE: PL/SQL statement help

From: Shamita Singh <shamita_s_at_yahoo.com>
Date: Thu, 24 Jul 2003 09:17:02 -0700 (PDT)
Message-Id: <26007.339461@fatcity.com>

--0-662613652-1059063422=:93043
Content-Type: text/plain; charset=us-ascii

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" <anelson_at_midf.com> 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:craighead_at_ameritech.net] 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.....

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 --0-662613652-1059063422=:93043
Content-Type: text/html; charset=us-ascii

<DIV>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.</DIV>
<DIV> </DIV>
<DIV>Shamita </DIV>
<DIV> </DIV>
<DIV>Shamita <BR><B><I>"Nelson, Allan" <anelson_at_midf.com></I></B> wrote:</DIV>
<DIV>
<BLOCKQUOTE style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid; WIDTH: 100%">
<META content="MSHTML 5.50.4923.2500" name=GENERATOR>
<STYLE></STYLE>

<DIV><SPAN class=867235215-24072003><FONT face=Arial color=#0000ff size=2>quantity is neither a column name or a valid column alias.  That's what your error is telling you.  </FONT></SPAN></DIV>
<DIV><SPAN class=867235215-24072003><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=867235215-24072003><FONT face=Arial color=#0000ff size=2>Allan</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> Milton C. Craighead, Jr. [mailto:craighead_at_ameritech.net] <BR><B>Sent:</B> Thursday, July 24, 2003 11:39 AM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B> PL/SQL statement help<BR><BR></FONT></DIV>
<DIV><FONT face=Arial size=2>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..... </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>1) where am I going wrong????</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>SQL> SELECT order#, customer#, address, city, state, zip,<BR>  2   orderdate, shipdate, shipstreet, shipcity, shipstate,<BR>  3   shipzip, item#, isbn, quantity<BR>  4  FROM customers NATURAL JOIN orders;<BR> shipzip, item#, isbn, quantity<BR>                       *<BR>ERROR at line 3:<BR>ORA-00904: "QUANTITY": invalid identifier <BR></FONT></DIV>
<DIV><FONT face=Arial size=2>2) I'm trying to format a report where the output should look like the following:</FONT></DIV>
<DIV><FONT face=Arial size=2>    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...</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Order#</FONT></DIV>
<DIV><FONT face=Arial size=2>Customer#</FONT></DIV>
<DIV><FONT face=Arial size=2>Customer Address</FONT></DIV>
<DIV><FONT face=Arial size=2>Customer City, State, Zip</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Order Date</FONT></DIV>
<DIV><FONT face=Arial size=2>Ship Date</FONT></DIV>
<DIV><FONT face=Arial size=2>Ship Street</FONT></DIV>
<DIV><FONT face=Arial size=2>Ship City, Ship State, Ship Zip</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>ITEM#        ISBN        QUANTY        RETAIL        TOTAL LINE</FONT></DIV>
<DIV><FONT face=Arial size=2>ITEM#        ISBN        QUANTY        RETAIL        TOTAL LINE</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>SET SERVEROUTPUT ON<BR>SQL> set linesize 300<BR>SQL> set pagesize 20<BR>SQL> ttitle CENTER 'The Law Report' SKIP 2<BR>SQL> btitle '(Confidential)' SKIP 2<BR>SQL> <BR>SQL> column item# heading 'ITEM#' format a10 truncate<BR>SQL> column isbn heading 'ISBN' format a15 truncate<BR>SQL> column quantity heading 'QUANTITY' format a6 truncate<BR>SQL> column retail heading 'RETAIL' format 990.00<BR>SQL> column total line heading 'TOTAL|LINE' format 990.00<BR>SP2-0158: unknown COLUMN option "line"<BR>SQL> <BR>SQL> Drop view bookPub;</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>View dropped.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>SQL> Create view bookPub<BR>  2  AS select count(b.title) as bookNum,  b.cost, b.pubid<BR>  3  from books b natural join orderitems o<BR>  4  group by b.pubid, b.cost;</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>View created.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>SQL> <BR>SQL> <BR>SQL> <BR>SQL> <BR>SQL> Drop view bookRev;</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>View dropped.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>SQL> Create view  bookRev<BR>  2  As select count(b.title) as bkNum,  sum(b.retail - b.cost) as rev, o.customer#<BR>  3  from orderitems i natural join books b natural join orders o<BR>  4  group by o.customer#;</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>View created.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>SQL> <BR>SQL> <BR>SQL> <BR>SQL> Drop view orderInfo;</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>View dropped.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>SQL> Create view  orderInfo<BR>  2  As<BR>  3  Select  c.customer#, o.order#, c.lastname, c.firstname, c.address,<BR>  4  o.shipdate, o.shipstreet, o.shipcity, o.shipstate,<BR>  5  o.shipzip, i.item#, i.isbn, i.quantity<BR>  6  from customers c, orders o, orderitems i Where c.customer# = o.customer#(+)<BR>  7  and o.order# = i.order#;</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>View created.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>SQL> <BR>SQL> CLEAR BREAK<BR>breaks cleared<BR>SQL> CLEAR COLUMN<BR>columns cleared<BR>SQL> <BR>SQL> SELECT order#, customer#, address, city, state, zip,<BR>  2   orderdate, shipdate, shipstreet, shipcity, shipstate,<BR>  3   shipzip, item#, isbn, quantity<BR>  4  FROM customers NATURAL JOIN orders;<BR> shipzip, item#, isbn, quantity<BR>                       *<BR>ERROR at line 3:<BR>ORA-00904: "QUANTITY": invalid identifier </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2><BR>SQL> <BR>SQL> SELECT item#, isbn, quantity, retail, sum(retail*quantity) "Total Line"<BR>  2  FROM customers NATURAL JOIN orders NATURAL JOIN orderitems<BR>  3   NATURAL JOIN books<BR>  4  GROUP BY item#, isbn, quantity, retail;<BR></FONT></DIV>
<DIV><FONT face=Arial size=2>3) last but not least I can not figure out how to create this statement at all:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>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 </FONT></DIV>
<DIV><FONT face=Arial size=2>maintain the status, and then only allow updates of that colum through a procedure. </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Again thanks for any and all assiatance....</FONT></DIV>
<DIV><FONT face=Arial size=2> </DIV></FONT>
<DIV><FONT face=Arial size=2>Regards,<BR>Milton C. Craighead, Jr.</FONT></DIV></BLOCKQUOTE>
<P>______________________________________________________________________________<BR>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]<BR></P></BLOCKQUOTE></DIV><BR><BR><DIV><FONT face="Comic Sans MS" color=mediumblue>Shamita</FONT></DIV><p><hr SIZE=1>
Received on Thu Jul 24 2003 - 11:17:02 CDT