Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3161
  • Last Modified:

PL/SQL Cursor and output issue

Here is my working program so far.

declare
   
v_value  number(8,2) := 0;
v_totalvalue  number(8,2) := 0;
v_itemid  ITEM.ITEM_ID%TYPE;  
v_itemdesc  ITEM.ITEM_DESC%TYPE;
v_itemsize  INVENTORY.ITEM_SIZE%TYPE;
v_color  INVENTORY.COLOR%TYPE;
v_price  INVENTORY.PRICE%TYPE;
v_qoh  INVENTORY.QOH%TYPE;

cursor itemid_cursor is
  select ITEM_ID, ITEM_DESC, ITEM_SIZE, COLOR, PRICE, QOH
  from ITEM natural join INVENTORY
  where ITEM_ID = v_itemid;

begin
     
select ITEM_ID
  into v_itemid
  from Phase2_Q1_Input
  where (ITEM_ID = 559);
   
dbms_output.put_line ('Item ID: ' || v_itemid);

open itemid_cursor;
     
loop
     
fetch itemid_cursor into v_itemid, v_itemdesc, v_itemsize, v_color, v_price, v_qoh;
v_value := v_price*v_qoh;
exit when itemid_cursor%NOTFOUND;

v_totalvalue := v_totalvalue + v_value ;
   
dbms_output.put_line (v_itemdesc || '  ' || v_itemsize || '   ' || v_color || '    ' ||

to_char(v_price, '$999.99') || '    ' || v_qoh || '    ' || to_char(V_Value, '$99,999.99'));
 
end loop;
   
dbms_output.put_line ('Total Value: ' || to_char(v_totalvalue, '$99,999.99'));  
     
end;
----------------------------------------------------------------------------------------------

Two things I am looking for are:

Phase2_Q1_Input has 4 different Item Id's loaded in the table.  I need to read each one of the item id's and output them like the single output I get from the above code.  Here it is:
-------------------------------------------------------------
Item ID: 559
Men's Expedition Parka  S   Spruce     $199.95    114     $22,794.30
Men's Expedition Parka  M   Spruce     $199.95    17      $3,399.15
Men's Expedition Parka  L   Spruce     $209.95    0           $.00
Men's Expedition Parka  XL   Spruce     $209.95    12      $2,519.40
Total Value:  $28,712.85
---------------------------------------------------------------

So the output should have 4 different Item Id's listed.

The second part of the problem is that 1 of the 4 Item Id's is invalid(not found in the original table) and the program needs to recognize this and output a statement that that Item Id is not found.

0
dcs001
Asked:
dcs001
  • 4
  • 3
  • 2
  • +1
1 Solution
 
ora-dbaCommented:
Hello,
please try the following code:
declare
   
v_value  number(8,2) := 0;
v_totalvalue  number(8,2) := 0;
v_itemid  ITEM.ITEM_ID%TYPE;  
v_itemdesc  ITEM.ITEM_DESC%TYPE;
v_itemsize  INVENTORY.ITEM_SIZE%TYPE;
v_color  INVENTORY.COLOR%TYPE;
v_price  INVENTORY.PRICE%TYPE;
v_qoh  INVENTORY.QOH%TYPE;

cursor Phase2 is
  select ITEM_ID
  from Phase2_Q1_Input;
 
cursor itemid_cursor(itemid ITEM.ITEM_ID%TYPE) is
  select ITEM_ID, ITEM_DESC, ITEM_SIZE, COLOR, PRICE, QOH
  from ITEM natural join INVENTORY
  where ITEM_ID = itemid;

begin

for c in Plase2 loop
   dbms_output.put_line ('Item ID: ' || c.itemid);
   open itemid_cursor(c.itemid);
   loop
     fetch itemid_cursor(c.itemid) into v_itemid, v_itemdesc, v_itemsize, v_color, v_price, v_qoh;
     v_value := v_price*v_qoh;
     exit when itemid_cursor%NOTFOUND;

     if itemid_cursor%ROWCOUNT = 0 then
        dbms_output.put_line('Invalid item id');
     else

         v_totalvalue := v_totalvalue + v_value ;
         dbms_output.put_line (v_itemdesc || '  ' || v_itemsize || '   ' || v_color || '    ' ||
         to_char(v_price, '$999.99') || '    ' || v_qoh || '    ' || to_char(V_Value, '$99,999.99'));
     end if;
   end loop;
   close itemid_cursor;
   dbms_output.put_line ('Total Value: ' || to_char(v_totalvalue, '$99,999.99'));  
end loop;    
end;
0
 
seazodiacCommented:
@dcs001:

your code has done the first part, pulling the four items of one Item ID, right?
the second part is very easy:

just do this at the end of the cursor loop:
-----------------------------------------------------------------------------------------------------------------
loop
   
fetch itemid_cursor into v_itemid, v_itemdesc, v_itemsize, v_color, v_price, v_qoh;
v_value := v_price*v_qoh;
exit when itemid_cursor%NOTFOUND;

v_totalvalue := v_totalvalue + v_value ;
   
dbms_output.put_line (v_itemdesc || '  ' || v_itemsize || '   ' || v_color || '    ' ||

to_char(v_price, '$999.99') || '    ' || v_qoh || '    ' || to_char(V_Value, '$99,999.99'));

end loop;
   
dbms_output.put_line ('Total Value: ' || to_char(v_totalvalue, '$99,999.99'));  
   
end;

---------------------------------------------------------------------------------------------------------------

--Add these three lines



if (itemid_cursor%ROWCOUNT <4) then
dbms_output.print_line ('this item id '||  v_itemid || ' has less than 4 items');

close itemid_cursor;




0
 
seazodiacCommented:
sorry it should be four lines:

if (itemid_cursor%ROWCOUNT <4) then
dbms_output.print_line ('this item id '||  v_itemid || ' has less than 4 items');
end if;
close itemid_cursor;

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
dcs001Author Commented:
I get this error from ora-dba response:

ERROR at line 27:
ORA-06550: line 27, column 25:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
. into bulk

Seazodiac, your response did not output all of the item id's from the Phase2_Q1_Input table, only the first one.  There are 4 item id's in this table, one of them is invalid.
0
 
dcs001Author Commented:
seazodiac said, "your code has done the first part, pulling the four items of one Item ID, right?"

I am actually trying to pull four ITEM ID's from an existing table with many Item ID's.
0
 
dcs001Author Commented:
Output should look like this:
----------------------------------
Item ID: 559
Men's Expedition Parka  S   Spruce     $199.95    114     $22,794.30
Men's Expedition Parka  M   Spruce     $199.95    17      $3,399.15
Men's Expedition Parka  L   Spruce     $209.95    0           $.00
Men's Expedition Parka  XL   Spruce     $209.95    12      $2,519.40
Total Value:  $28,712.85

Item ID: 894
Women's Hiking Shorts  S   Khaki      $29.95    150      $4,492.50
Women's Hiking Shorts  M   Khaki      $29.95    147      $4,402.65
Women's Hiking Shorts  L   Khaki      $29.95    0           $.00
Women's Hiking Shorts  S   Navy      $29.95    139      $4,163.05
Women's Hiking Shorts  M   Navy      $29.95    137      $4,103.15
Women's Hiking Shorts  L   Navy      $29.95    115      $3,444.25
Total Value:  $20,605.60

Item ID: 897
Women's Fleece Pullover  S   Eggplant      $59.95    135      $8,093.25
Women's Fleece Pullover  M   Eggplant      $59.95    168     $10,071.60
Women's Fleece Pullover  L   Eggplant      $59.95    187     $11,210.65
Women's Fleece Pullover  S   Royal      $59.95    0           $.00
Women's Fleece Pullover  M   Royal      $59.95    124      $7,433.80
Women's Fleece Pullover  L   Royal      $59.95    112      $6,714.40
Total Value:  $43,523.70

Item ID: 111 is not found in the database
----------------------------------------------------

This is where i'm having trouble.  it works for one item id when defined this way:
select ITEM_ID
  into v_itemid
  from Phase2_Q1_Input;
  where (ITEM_ID = 894);

I need to get them all at the same time, and produce an error message when an item number is not found.
0
 
ora-dbaCommented:
I'm sorry the following code should not give you an error:
declare
   
v_value  number(8,2) := 0;
v_totalvalue  number(8,2) := 0;
v_itemid  ITEM.ITEM_ID%TYPE;  
v_itemdesc  ITEM.ITEM_DESC%TYPE;
v_itemsize  INVENTORY.ITEM_SIZE%TYPE;
v_color  INVENTORY.COLOR%TYPE;
v_price  INVENTORY.PRICE%TYPE;
v_qoh  INVENTORY.QOH%TYPE;

cursor Phase2 is
  select ITEM_ID
  from Phase2_Q1_Input;
 
cursor itemid_cursor(itemid ITEM.ITEM_ID%TYPE) is
  select ITEM_ID, ITEM_DESC, ITEM_SIZE, COLOR, PRICE, QOH
  from ITEM natural join INVENTORY
  where ITEM_ID = itemid;

begin

for c in Plase2 loop
   dbms_output.put_line ('Item ID: ' || c.itemid);
   open itemid_cursor(c.itemid);
   loop
     fetch itemid_cursor into v_itemid, v_itemdesc, v_itemsize, v_color, v_price, v_qoh;
     v_value := v_price*v_qoh;
     exit when itemid_cursor%NOTFOUND;

     if itemid_cursor%ROWCOUNT = 0 then
        dbms_output.put_line('Invalid item id');
     else

         v_totalvalue := v_totalvalue + v_value ;
         dbms_output.put_line (v_itemdesc || '  ' || v_itemsize || '   ' || v_color || '    ' ||
         to_char(v_price, '$999.99') || '    ' || v_qoh || '    ' || to_char(V_Value, '$99,999.99'));
     end if;
   end loop;
   close itemid_cursor;
   dbms_output.put_line ('Total Value: ' || to_char(v_totalvalue, '$99,999.99'));  
end loop;    
end;
0
 
dcs001Author Commented:
I get this erro with your code.

ERROR at line 20:
ORA-06550: line 20, column 19:
PL/SQL: ORA-00904: "ITEMID": invalid identifier
ORA-06550: line 18, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 25, column 43:
PLS-00302: component 'ITEMID' must be declared
ORA-06550: line 25, column 4:
PL/SQL: Statement ignored
ORA-06550: line 26, column 25:
PLS-00302: component 'ITEMID' must be declared
ORA-06550: line 26, column 4:
PL/SQL: SQL Statement ignored
0
 
annamalai77Commented:
hi

the below method should solve ur problem.
i assume that ur itemid is varchar field. in case it is number
then change according and in the dbms line
use to_char(i.itemid).

declare
v_value  number(8,2) := 0;
v_totalvalue  number(8,2) := 0;
v_itemid  ITEM.ITEM_ID%TYPE;  
v_itemdesc  ITEM.ITEM_DESC%TYPE;
v_itemsize  INVENTORY.ITEM_SIZE%TYPE;
v_color  INVENTORY.COLOR%TYPE;
v_price  INVENTORY.PRICE%TYPE;
v_qoh  INVENTORY.QOH%TYPE;

cursor c1 is
select ITEM_ID
  from Phase2_Q1_Input
order by item_id;
   
cursor itemid_cursor (v_itemid in number) is
  select ITEM_ID, ITEM_DESC, ITEM_SIZE, COLOR, PRICE, QOH
  from ITEM natural join INVENTORY
  where ITEM_ID = v_itemid;

begin
for i in c1
loop
  dbms_output.put_line ('Item ID: ' || i.itemid);
  for j in  itemid_cursor(i.itemid)
  loop
     v_itemid := j.itemid;
     v_itemdesc := j.item_desc;
     v_itemsize := j.item_size;
     v_color := j.color;
     v_price := j.price;
     v_qoh   := j.qoh;

     v_value := v_price*v_qoh;

     v_totalvalue := v_totalvalue + v_value ;
   
     dbms_output.put_line (v_itemdesc || '  ' || v_itemsize   || '   ' || v_color || '    ' || to_char(v_price, '$999.99') || '    ' || v_qoh || '    ' || to_char(V_Value, '$99,999.99'));
  end loop;
   
dbms_output.put_line ('Total Value: ' || to_char(v_totalvalue, '$99,999.99'));  

v_value := 0;
v_totalvalue := 0;
end loop;
end;
/

regards
annamalai
0
 
ora-dbaCommented:
I'm sorry again but I don't have your table structure. Please try this:

declare
   
v_value  number(8,2) := 0;
v_totalvalue  number(8,2) := 0;
v_itemid  ITEM.ITEM_ID%TYPE;  
v_itemdesc  ITEM.ITEM_DESC%TYPE;
v_itemsize  INVENTORY.ITEM_SIZE%TYPE;
v_color  INVENTORY.COLOR%TYPE;
v_price  INVENTORY.PRICE%TYPE;
v_qoh  INVENTORY.QOH%TYPE;

cursor Phase2 is
  select ITEM_ID
  from Phase2_Q1_Input;
 
cursor itemid_cursor(itemid ITEM.ITEM_ID%TYPE) is
  select ITEM_ID, ITEM_DESC, ITEM_SIZE, COLOR, PRICE, QOH
  from ITEM natural join INVENTORY
  where ITEM_ID = itemid;

begin

for c in Plase2 loop
   dbms_output.put_line ('Item ID: ' || c.item_id);
   open itemid_cursor(c.item_id);
   loop
     fetch itemid_cursor into v_itemid, v_itemdesc, v_itemsize, v_color, v_price, v_qoh;
     v_value := v_price*v_qoh;
     exit when itemid_cursor%NOTFOUND;

     if itemid_cursor%ROWCOUNT = 0 then
        dbms_output.put_line('Invalid item id');
     else

         v_totalvalue := v_totalvalue + v_value ;
         dbms_output.put_line (v_itemdesc || '  ' || v_itemsize || '   ' || v_color || '    ' ||
         to_char(v_price, '$999.99') || '    ' || v_qoh || '    ' || to_char(V_Value, '$99,999.99'));
     end if;
   end loop;
   close itemid_cursor;
   dbms_output.put_line ('Total Value: ' || to_char(v_totalvalue, '$99,999.99'));  
end loop;    
end;
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now