I m try to solve this problem
Add a column named *reorder_quanity* to the *books* table that will hold a value up to 999 with a default value of 10.
4. Update the reorder quantity of all books to 10.
5. Update the quantity ordered to 1 for all books pending shipment.
This is my code:(
when run my code this error i m getting
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 9
ALTER TABLE books
ADD(reorder_quantity NUMBER(3) DEFAULT 10);
CREATE TABLE reorder(REORDER# number(6) not null,REORDER_DATE Date,ISBN VARCHAR(10),QUANTITY INT,RECEIPT_DATE Date);
ALTER TABLE reorder ADD primary key (REORDER#);
ALTER TABLE reorder ADD CONSTRAINT fk_rerOrders FOREIGN KEY (ISBN)
REFERENCES books(ISBN);
DECLARE
lv_title_txt books.title%TYPE;
lv_reorder_num books.reorder_quantity%TYPE;
lv_shipdate_date orders.order#%TYPE;
BEGIN
SELECT DISTINCT title,reorder_quantity
INTO lv_title_txt,lv_reorder_num
FROM books JOIN orderitems USING(isbn)
WHERE order# IN
(SELECT order#
FROM orders
WHERE shipdate IS NULL);
IF lv_shipdate_date = NULL THEN
UPDATE books
SET reorder_quantity = 1;
END IF;
END;
you are getting multiple rows there in line number 19.
that's why this error is generating
do one thing --
add following query at line number 16
for this kind of multiple row fetching you have to use cursor ... you can't give these multiple value to a variable by into clouse
cursor c_1 is
SELECT DISTINCT title,reorder_quantity
FROM books JOIN orderitems
USING(isbn)WHERE ORDER# IN
(SELECT ORDER#
FROM orders
WHERE shipdate IS NULL);
type l_cursor is table of c_1;
v_cursor l_cursor;
then you have to apply loop in your code and for every instance you have to check for null value if any...
i am not getting this points
4. Update the reorder quantity of all books to 10.
5. Update the quantity ordered to 1 for all books pending shipment.
please check this as well ... you want to assign 10 as quantity or 1 when null value found????
you are getting multiple rows there in line number 19.
that's why this error is generating
do one thing --
add following query at line number 16
for this kind of multiple row fetching you have to use cursor ... you can't give these multiple value to a variable by into clouse
cursor c_1 is
SELECT DISTINCT title,reorder_quantity
FROM books JOIN orderitems
USING(isbn)WHERE ORDER# IN
(SELECT ORDER#
FROM orders
WHERE shipdate IS NULL);
type l_cursor is table of c_1;
v_cursor l_cursor;
then you have to apply loop in your code and for every instance you have to check for null value if any...
i am not getting this points
4. Update the reorder quantity of all books to 10.
5. Update the quantity ordered to 1 for all books pending shipment.
please check this as well ... you want to assign 10 as quantity or 1 when null value found????
When the shipdate is NULL assign 1 but if IS NOT NULL LEAVE 10, Just update table when the shipdate is NULL
You're getting a bunch of Titles and a bunch of ReOrder_Quantities,
and trying to fit all of them into a single LV_Title_Txt and LV_ReOrder_Num.
Look up CURSORs in the PL/SQL Reference.
Do a For Loop and process each record.
