Please help me. I am trying to compare two elements in my PL/SQL lists in “TABLE OF NUMBER INDEX BY PLS_INTEGER”. I have two elements saved to a list if they exist. Then am looping through a table and want to compare both elements with the same columns in the table. At the moment, I can only compare one element and id does not capture both elements. The idea is to combine both lists. So in the table below, I want to get where the combination of WHS and ITEM in table CAS does not exist in MAg and PART in table EASY. At the moment, in my code, it only returns one line but I want both lines in pink. this is because am comparing just the item and not the combination of item and whs. Please, I will be grateful if you could show me how I can combine the elements in the lists and compare them with the two columns in the table CAS.
Many thanks for your help.
NB: Just in case this turns out to be a duplicate post, my apologies, I posted 1 initially and it disappears.
declare
TYPE prod_list_t IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
prod_list prod_list_t;
prod_mag prod_list_t;
CURSOR c_easy IS
SELECT mag,part,qty
FROM EASY
ORDER BY mag,part;
CURSOR c_cas IS
SELECT * FROM cas m
ORDER BY m.whs;
begin
FOR e IN c_easy LOOP
-- Add to prod_list for later process of values NOT in the file.
prod_list(e.part) := e.part;
prod_mag(e.mag) := e.mag;
END LOOP;
FOR r IN c_cas LOOP
IF NOT prod_list.exists(r.item) THEN -- product with stock in CAS not found in file
dbms_output.put_line( r.whs||'*'||r.item||'*'||r.qty);
END IF;
END LOOP;
end;
Current output = 750*49125777*3
It should be
750*49125777*3
790*49127804*0 - Not currently showing this line
create table EASY
(
mag VARCHAR2(3),
part NUMBER(9),
qty NUMBER(10,2)
);
create table CAS
(
whs VARCHAR2(3),
item NUMBER(9),
qty NUMBER(10,2)
);
Insert into EASY (MAG,PART,QTY) values ('750',49126363,2);
Insert into EASY (MAG,PART,QTY) values ('758',70002064,0);
Insert into EASY (MAG,PART,QTY) values ('768',43035700,10);
Insert into EASY (MAG,PART,QTY) values ('751',49116337,0);
Insert into EASY (MAG,PART,QTY) values ('768',49127945,0);
Insert into EASY (MAG,PART,QTY) values ('780',49127611,3);
Insert into EASY (MAG,PART,QTY) values ('799',49127804,3);
Insert into CAS (WHS,ITEM,QTY) values ('750',49126363,2);
Insert into CAS (WHS,ITEM,QTY) values ('758',70002064,0);
Insert into CAS (WHS,ITEM,QTY) values ('768',43035700,10);
Insert into CAS (WHS,ITEM,QTY) values ('751',49116337,0);
Insert into CAS (WHS,ITEM,QTY) values ('768',49127945,0);
Insert into CAS (WHS,ITEM,QTY) values ('780',49127611,3);
Insert into CAS (WHS,ITEM,QTY) values ('750',49125777,3);
Insert into CAS (WHS,ITEM,QTY) values ('758',49127804,0);
Insert into CAS (WHS,ITEM,QTY) values ('790',49127804,0);
Insert into CAS (WHS,ITEM,QTY) values ('799',49127804,0);