Member Avatar for agaba

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;
Edited by agaba because: n/a
  • 4 Contributors
  • 6 Replies
  • 215 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by hfx642
Member Avatar for debasisdas

I think your data is not proper.

also change the following line to

IF lv_shipdate_date = NULL


IF lv_shipdate_date IS NULL

Member Avatar for pratik_garg

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

Edited by pratik_garg because: n/a
Member Avatar for agaba

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

Member Avatar for agaba

I think your data is not proper.

also change the following line to

IF lv_shipdate_date = NULL


IF lv_shipdate_date IS NULL

what you mean by not proper ??
Thanks

Member Avatar for pratik_garg

When the shipdate is NULL assign 1 but if IS NOT NULL LEAVE 10, Just update table when the shipdate is NULL

okay then you can use nvl2 function over there

as per your requrement your nvl2 function should be like

nvl2(reorder_quantity,10,1)

it suppose to be in update command
when you are going to run update table just put this nvl2 function to set record_quantity value..

Edited by pratik_garg because: n/a
Member Avatar for hfx642

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.