| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> PL/SQL statement help
This is a multi-part message in MIME format.
------=_NextPart_000_0047_01C351D8.3EA74040 Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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.....=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
*
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>=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.00SP2-0158: unknown COLUMN option "line"
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>=20 SQL>=20 SQL>=20 SQL>=20 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>=20 SQL>=20 SQL>=20 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# =3D =
o.customer#(+)
7 and o.order# =3D i.order#;
View created.
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
*
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;
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=20 maintain the status, and then only allow updates of that colum through a = procedure.=20
Again thanks for any and all assiatance....
Regards,
Milton C. Craighead, Jr.
------=_NextPart_000_0047_01C351D8.3EA74040
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1170" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>I'm having no luck resolving the =
following PL/SQL=20
errors for a final exam class project. We do not =
have access=20
to metalink nor do we have access to Oracle PL/SQL documentation. =
We are=20
being taught from a PL/SQL text book that does not provide ora error =
message=20
information or where to find them... Any and all assistance =
will be=20
greatly appreciated..... </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>1) where am I going =
wrong????</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>SQL> SELECT order#, customer#, =
address, city,=20
state, zip,<BR> 2 orderdate, shipdate, shipstreet, =
shipcity,=20
shipstate,<BR> 3 shipzip, item#, isbn, =
quantity<BR> =20
4 FROM customers NATURAL JOIN orders;<BR> shipzip, item#, =
isbn,=20
quantity<BR> &=
nbsp; =20
*<BR>ERROR at line 3:<BR>ORA-00904: "QUANTITY": invalid identifier=20
<BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>2) I'm trying to format a report where =
the output=20
should look like the following:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> I'm able to format =
the item#,=20
isbn, quanty, retail, and total line with out error but Im getting an =
error=20
message in my veiw...</FONT></DIV>
<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>ITEM# =20
ISBN QUANTY =20
RETAIL =
TOTAL=20
LINE</FONT></DIV>
<DIV><FONT face=3DArial=20
size=3D2>ITEM# ISBN &=
nbsp; =20
QUANTY =20
RETAIL TOTAL LINE</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 =
linesize=20
300<BR>SQL> set pagesize 20<BR>SQL> ttitle CENTER 'The Law Report' =
SKIP=20
2<BR>SQL> btitle '(Confidential)' SKIP 2<BR>SQL> <BR>SQL> =
column item#=20
heading 'ITEM#' format a10 truncate<BR>SQL> column isbn heading =
'ISBN' format=20
a15 truncate<BR>SQL> column quantity heading 'QUANTITY' format a6=20
truncate<BR>SQL> column retail heading 'RETAIL' format =
990.00<BR>SQL>=20
column total line heading 'TOTAL|LINE' format 990.00<BR>SP2-0158: =
unknown COLUMN=20
option "line"<BR>SQL> <BR>SQL> Drop view bookPub;</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 bookPub<BR> =
2 AS=20
select count(b.title) as bookNum, b.cost, b.pubid<BR> =
3 from=20
books b natural join orderitems o<BR> 4 group by b.pubid,=20
b.cost;</FONT></DIV>
<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>=20
<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> 2 =20
As select count(b.title) as bkNum, sum(b.retail - b.cost) as rev,=20
o.customer#<BR> 3 from orderitems i natural join books b =
natural=20
join orders o<BR> 4 group by o.customer#;</FONT></DIV>
<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> Drop=20
view orderInfo;</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 =
orderInfo<BR> =20
2 As<BR> 3 Select c.customer#, o.order#, =
c.lastname,=20 c.firstname, c.address,<BR> 4 o.shipdate, o.shipstreet, = o.shipcity,=20 o.shipstate,<BR> 5 o.shipzip, i.item#, i.isbn, = i.quantity<BR> =20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>maintain the status, and then only =
allow updates of=20
that colum through a procedure. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
Received on Thu Jul 24 2003 - 10:39:30 CDT
 |
 |