display item and inventory information for a user defined item ID

Question

So my task like the title states is to display the item and inventory information from a user defined item ID. I have to write a single-row select statement that returns Item_desc and then display it as a header row. Assuming the user has entered an item id of 2 via use of an "&" (ampersand) variable, format the output as follows: This is for PLSQL btw

Item ID: 2 Item Description: 3-Season Tent

I also have to create an explicit or implicit cursor that returns and displays the size, color, current price, quantity on hand, and total value (price*quantity on hand) for each individual inventory item. So far I have this but I have no clue if I am going in the right direction.

--CORRECT CODE IS LISTED BELOW WITH NEXT PROBLEM

UPDATE!!: OK SO I GOT THE CODE TO WORK BUT FOR THE NEXT PART FOR WHICH IM STUCK IS TO CREATE A VARIABLE THAT CALCULATES THE TOTAL VALUE FOR ALL INVENTORY ITEMS AND THEN DISPLAY IT AFTER ALL ROWS ARE PROCESSED....SHOULD LOOKS SOMETHING LIKE THIS:

TOTAL VALUE: --WHATEVER TOTAL VALUE ITS SUPPOSED TO BE FOR THE PARTICULAR ITEM

SO FAR I'VE GOT THIS BUT ITS NOT CALCULATING CORRECTLY...

declare
item_descript   item.item_desc%type;
iItem_id    item.item_id%type;
inItemid    inventory.item_id%type;
invcolor    inventory.color%type;
invsize     inventory.inv_size%type;
invprice    inventory.inv_price%type;
invqoh      inventory.inv_qoh%type;
value       number;
newval      number;
totalval    number;

cursor c1 IS
SELECT item.item_id, item.item_desc, inventory.item_id, inventory.color, inventory.inv_size, inventory.inv_price, inventory.inv_qoh
from item join inventory on item.item_id=inventory.item_id
where item.item_id=&item_id;




begin


open c1;

    fetch c1 into iItem_id, item_descript, inItemid, invcolor, invsize, invprice, invqoh;
    --exit when c1%notfound;

    DBMS_OUTPUT.PUT_LINE('Item ID: '||iItem_id||' Item Description: '||item_descript);


close c1;


open c1;
loop

    fetch c1 into iItem_id, item_descript, inItemid, invcolor, invsize, invprice, invqoh;



        exit when c1%notfound;

        value := (invprice*invqoh);

        newval := value; -- THIS IS WHERE I'M NOT SURE AS HOW TO CALCULATE FOR TOTAL VALUE???

        totalval := value + newval; --NOT SURE ?????



    DBMS_OUTPUT.PUT_LINE('================');
    DBMS_OUTPUT.PUT_LINE('Size: '||invsize);
    DBMS_OUTPUT.PUT_LINE('Color: '||invcolor);
    DBMS_OUTPUT.PUT_LINE('Price: '||invprice);
    DBMS_OUTPUT.PUT_LINE('QOH: '||invqoh);
    DBMS_OUTPUT.PUT_LINE('Value: '||value);

end loop;




    DBMS_OUTPUT.PUT_LINE('TOTAL VALUE: '||totalval); --THIS OUTPUT SHOULD BE CORRECT IF ONLY IT WOULD CALCULATE CORRECTLY




close c1;



commit;
end;
/

score 0 · Answer 1 · answered Apr 18 '14 at 05:07

Hi I think the problem is with the select query in the cursor. Please try this and let me know for any issues.


DECLARE
  item_descript item.item_desc%type;
  iItem_id item.item_id%type;
  inItemid inventory.item_id%type;
  invcolor inventory.color%type;
  invsize inventory.inv_size%type;
  invprice inventory.inv_price%type;
  invqoh inventory.inv_qoh%type;
  value NUMBER;
  CURSOR c1
  IS
    SELECT item.item_id,
      item.item_desc,
      inventory.item_id,
      inventory.color,
      inventory.inv_size,
      inventory.inv_price,
      inventory.inv_qoh
    FROM item,
      inventory
    WHERE item.item_id=inventory.item_id
    AND item_id       =
      &item_id;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1
    INTO iItem_id,
      item_descript,
      inItemid,
      invcolor,
      invsize,
      invprice,
      invqoh;
    EXIT
  WHEN c1%notfound;
    IF iItem_id=inItemid THEN
      value   :=(invprice*invqoh);
      SELECT item_desc
      INTO item_descript
      FROM item
      ,inventory
      WHERE item.item_id  =inventory.item_id
      AND item.item_id  =iItem_id
      AND inventory.item_id=inItemid;
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Item ID: '||iItem_id||'Item Description: '||item_descript);
  DBMS_OUTPUT.PUT_LINE('================');
  DBMS_OUTPUT.PUT_LINE('Size: '||invsize);
  DMBS_OUTPUT.PUT_LINE('Color: '||invcolor);
  DBMS_OUTPUT.PUT_LINE('Price: '||invprice);
  DBMS_OUTPUT.PUT_LINE('QOH: '||invqoh);
  DBMS_OUTPUT.PUT_LINE('Value: '||value);
END;

I realized I had some extra coding in there that was not necessary. Also changed the where clause in the cursor select statement to account for the user defined variable via &ampersand — Jongu, Apr 19 '14 at 04:45
so.is.it.working.now.If.my.answer.helped.you.can.you.pl.accept.my.answer.Thanks — Avrajit, Apr 20 '14 at 08:01

score 0 · Answer 2 · answered Apr 19 '14 at 04:46

declare
item_descript   item.item_desc%type;
iItem_id    item.item_id%type;
inItemid    inventory.item_id%type;
inv   color inventory.color%type;
invsize     inventory.inv_size%type;
invprice    inventory.inv_price%type;
invqoh      inventory.inv_qoh%type;
value       number;
--totalval  number;

cursor c1 IS
SELECT item.item_id, item.item_desc, inventory.item_id, inventory.color, inventory.inv_size, inventory.inv_price, inventory.inv_qoh
from item join inventory on item.item_id=inventory.item_id
where item.item_id=&item_id;




begin


open c1;

    fetch c1 into iItem_id, item_descript, inItemid, invcolor, invsize, invprice, invqoh;
    --exit when c1%notfound;

    DBMS_OUTPUT.PUT_LINE('Item ID: '||iItem_id||'Item Description: '||item_descript);


close c1;


open c1;
loop

    fetch c1 into iItem_id, item_descript, inItemid, invcolor, invsize, invprice, invqoh;



        exit when c1%notfound;

        value := (invprice*invqoh);









    DBMS_OUTPUT.PUT_LINE('================');
    DBMS_OUTPUT.PUT_LINE('Size: '||invsize);
    DBMS_OUTPUT.PUT_LINE('Color: '||invcolor);
    DBMS_OUTPUT.PUT_LINE('Price: '||invprice);
    DBMS_OUTPUT.PUT_LINE('QOH: '||invqoh);
    DBMS_OUTPUT.PUT_LINE('Value: '||value);

end loop;   





close c1;
commit;
end;
/

score 0 · Answer 3 · answered Apr 20 '14 at 08:17

hey can you try this and let me know for any issues.Thanks

declare
item_descript   item.item_desc%type;
iItem_id    item.item_id%type;
inItemid    inventory.item_id%type;
invcolor    inventory.color%type;
invsize     inventory.inv_size%type;
invprice    inventory.inv_price%type;
invqoh      inventory.inv_qoh%type;
value       number;
newval      number;
totalval    number;

cursor c1 IS
SELECT item.item_id, item.item_desc, inventory.item_id, inventory.color, inventory.inv_size, inventory.inv_price, inventory.inv_qoh
from item join inventory on item.item_id=inventory.item_id
where item.item_id=&item_id;




begin


open c1;

    fetch c1 into iItem_id, item_descript, inItemid, invcolor, invsize, invprice, invqoh;
    --exit when c1%notfound;

    DBMS_OUTPUT.PUT_LINE('Item ID: '||iItem_id||' Item Description: '||item_descript);


close c1;


open c1;
loop

    fetch c1 into iItem_id, item_descript, inItemid, invcolor, invsize, invprice, invqoh;



        exit when c1%notfound;

        totalval := totalval + (invprice*invqoh);

        value:= (invprice*invqoh);

       -- newval := value; -- THIS IS WHERE I'M NOT SURE AS HOW TO CALCULATE FOR TOTAL VALUE???

        --totalval := value + newval; --NOT SURE ?????



    DBMS_OUTPUT.PUT_LINE('================');
    DBMS_OUTPUT.PUT_LINE('Size: '||invsize);
    DBMS_OUTPUT.PUT_LINE('Color: '||invcolor);
    DBMS_OUTPUT.PUT_LINE('Price: '||invprice);
    DBMS_OUTPUT.PUT_LINE('QOH: '||invqoh);
    DBMS_OUTPUT.PUT_LINE('Value: '||value);

end loop;




    DBMS_OUTPUT.PUT_LINE('TOTAL VALUE: '||totalval); --THIS OUTPUT SHOULD BE CORRECT IF ONLY IT WOULD CALCULATE CORRECTLY




close c1;



commit;
end;

display item and inventory information for a user defined item ID

3 Answers3