Message-Id: <26007.339453@fatcity.com> From: "Milton C. Craighead, Jr." Date: Thu, 24 Jul 2003 11:39:30 -0400 Subject: 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 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=20 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.00 SP2-0158: unknown COLUMN option "line" SQL>=20 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>=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 * ERROR at line 3: ORA-00904: "QUANTITY": invalid identifier=20 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

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.....

1) where am I going = wrong????

SQL> SELECT order#, customer#, = address, city,=20 state, zip,
2   orderdate, shipdate, shipstreet, = shipcity,=20 shipstate,
3   shipzip, item#, isbn, = quantity
=20 4 FROM customers NATURAL JOIN orders;
shipzip, item#, = isbn,=20 quantity
          &= nbsp;           =20 *
ERROR at line 3:
ORA-00904: "QUANTITY": invalid identifier=20

2) I'm trying to format a report where = the output=20 should look like the following:

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...

Order#

Customer#

Customer Address

Customer City, State, Zip

Order Date

Ship Date

Ship Street

Ship City, Ship State, Ship = Zip

ITEM# =20 ISBN QUANTY =20 RETAIL = TOTAL=20 LINE

ITEM# ISBN &= nbsp; =20 QUANTY =20 RETAIL TOTAL LINE

SET SERVEROUTPUT ON
SQL> set = linesize=20 300
SQL> set pagesize 20
SQL> ttitle CENTER 'The Law Report' = SKIP=20 2
SQL> btitle '(Confidential)' SKIP 2
SQL>
SQL> = column item#=20 heading 'ITEM#' format a10 truncate
SQL> column isbn heading = 'ISBN' format=20 a15 truncate
SQL> column quantity heading 'QUANTITY' format a6=20 truncate
SQL> column retail heading 'RETAIL' format = 990.00
SQL>=20 column total line heading 'TOTAL|LINE' format 990.00
SP2-0158: = unknown COLUMN=20 option "line"
SQL>
SQL> Drop view bookPub;

View dropped.

SQL> Create view bookPub
= 2 AS=20 select count(b.title) as bookNum, b.cost, b.pubid
= 3 from=20 books b natural join orderitems o
4 group by b.pubid,=20 b.cost;

View created.

SQL>
SQL>
SQL> =
SQL>=20
SQL> Drop view bookRev;

View dropped.

SQL> Create view = bookRev
2 =20 As select count(b.title) as bkNum, sum(b.retail - b.cost) as rev,=20 o.customer#
3 from orderitems i natural join books b = natural=20 join orders o
4 group by o.customer#;

View created.

SQL>
SQL>
SQL> =
SQL> Drop=20 view orderInfo;

View dropped.

SQL> Create view = orderInfo
=20 2 As
3 Select c.customer#, o.order#, = c.lastname,=20 c.firstname, c.address,
4 o.shipdate, o.shipstreet, = o.shipcity,=20 o.shipstate,
5 o.shipzip, i.item#, i.isbn, = i.quantity
=20 6 from customers c, orders o, orderitems i Where c.customer# =3D=20 o.customer#(+)
7 and o.order# =3D i.order#;

View created.

SQL>
SQL> CLEAR = BREAK
breaks=20 cleared
SQL> CLEAR COLUMN
columns cleared
SQL> =
SQL> SELECT=20 order#, customer#, address, city, state, zip,
2   = orderdate,=20 shipdate, shipstreet, shipcity, shipstate,
3   = shipzip,=20 item#, isbn, quantity
4 FROM customers NATURAL JOIN=20 orders;
shipzip, item#, isbn,=20 quantity
          &= nbsp;           =20 *
ERROR at line 3:
ORA-00904: "QUANTITY": invalid identifier =

SQL>
SQL> SELECT item#, = isbn,=20 quantity, retail, sum(retail*quantity) "Total Line"
2 = FROM=20 customers NATURAL JOIN orders NATURAL JOIN orderitems
= 3 =20 NATURAL JOIN books
4 GROUP BY item#, isbn, quantity,=20 retail;

3) last but not least I can not figure = out how to=20 create this statement at all:

Deletes are not allowed, but you will = be allowed to=20 de-activate orders. You will need to add a column to the orders table to =

maintain the status, and then only = allow updates of=20 that colum through a procedure.