| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: PL/SQL statement help
This is a multi-part message in MIME format.
------_=_NextPart_001_01C351FB.ABF49B37
Content-Type: text/plain;
charset=us-ascii
Content-Transfer-Encoding: quoted-printable
quantity is neither a column name or a valid column alias. That's what
your error is telling you. =20
=20
Allan
-----Original Message----- From: Milton C. Craighead, Jr. [mailto:craighead_at_ameritech.net]=20 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
=66inal 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.....=20
=20 1) where am I going wrong=3F=3F=3F=3F =20 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=20This 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= =
=09
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... =20 =20 Order# Customer# Customer Address Customer City, State, Zip =20 Order Date Ship Date Ship Street Ship City, Ship State, Ship Zip =20 ITEM# ISBN QUANTY RETAIL TOTAL LINE ITEM# ISBN QUANTY RETAIL TOTAL LINE =20 =20 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>=20 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>=20 SQL> Drop view bookPub; =20 View dropped. =20 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; =20 View created. =20 SQL>=20 SQL>=20 SQL>=20 SQL>=20 SQL> Drop view bookRev; =20 View dropped. =20 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#; =20 View created. =20 SQL>=20 SQL>=20 SQL>=20 SQL> Drop view orderInfo; =20 View dropped. =20 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#
=3D o.customer#(+)
7 and o.order# =3D i.order#; =20 View created. =20 SQL>=20 SQL> CLEAR BREAK breaks cleared SQL> CLEAR COLUMN columns cleared SQL>=20 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=20 =20
=09
SQL>=20 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;
=09
3) last but not least I can not figure out how to create this statement at all: =20 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=20 maintain the status, and then only allow updates of that colum through a procedure.=20 =20 Again thanks for any and all assiatance.... =20 Regards, Milton C. Craighead, Jr. ______________________________________________________________________________
------_=_NextPart_001_01C351FB.ABF49B37
Content-Type: text/html;
charset=us-ascii
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD><TITLE>Message</TITLE> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Dus-ascii"> <META content=3D"MSHTML 5.50.4923.2500" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><SPAN class=3D867235215-24072003><FONT face=3DArial color=3D#0000ff=20size=3D2>quantity is neither a column name or a valid column alias. = That's=20
<DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Order#</FONT></DIV> <DIV><FONT face=3DArial size=3D2>Customer#</FONT></DIV> <DIV><FONT face=3DArial size=3D2>Customer Address</FONT></DIV> <DIV><FONT face=3DArial size=3D2>Customer City, State, Zip</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Order Date</FONT></DIV> <DIV><FONT face=3DArial size=3D2>Ship Date</FONT></DIV> <DIV><FONT face=3DArial size=3D2>Ship Street</FONT></DIV> <DIV><FONT face=3DArial size=3D2>Ship City, Ship State, Ship =Zip</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>SET SERVEROUTPUT ON<BR>SQL> set linesiz=e=
<DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>View dropped.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>SQL> Create view bookPub<BR> =2 AS=20
<DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>View created.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>SQL> <BR>SQL> <BR>SQL> <BR>SQL>=;=
<BR>SQL> Drop view bookRev;</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>View dropped.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>SQL> Create view bookRev<BR> =;=
<DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>View created.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>SQL> <BR>SQL> <BR>SQL> <BR>SQL>=;=
<DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>View dropped.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>SQL> Create view =orderInfo<BR> =20
c.firstname, c.address,<BR> 4 o.shipdate, o.shipstreet,=20 o.shipcity, o.shipstate,<BR> 5 o.shipzip, i.item#, i.isbn,=20 i.quantity<BR> 6 from customers c, orders o, orderitems i Where=
<DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>View created.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>SQL> <BR>SQL> CLEAR BREAK<BR>breaks=
</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><BR>SQL> <BR>SQL> SELECT item#, isbn=,=
<P>__________________________________________________________________________=____<br>
 |
 |