| help with logic [message #373376] |
Fri, 13 April 2001 09:36  |
sandeep
Messages: 110
Registered: October 2000
|
Senior Member |
|
|
If one table has a,b,c,c,c,d,e and another table has b,c. how do I extract a,c,c,d,e.
thanks in advance for help.
|
|
|
|
| Re: help with logic [message #373380 is a reply to message #373376] |
Fri, 13 April 2001 10:16  |
sandeep
Messages: 110
Registered: October 2000
|
Senior Member |
|
|
There are only seven fields in table 1. And only 2 fields in table 2.
I want fields 1,3,4,6,7 from table 1.
Note that c is the same value repeated 3 times in table 1. I need to eliminate it only once from table 1 along with the value in table 2.
In other words,I want everything that is in table 1 but not in table 2. and if there are duplicates or triplicates, I want to eliminate them from table 1 only as many times as they appear in table 2.
|
|
|
|
| Re: help with logic [message #373575 is a reply to message #373376] |
Tue, 24 April 2001 07:14 |
lp
Messages: 8
Registered: April 2001
|
Junior Member |
|
|
You could do this thru pl/sql code
Assume Tbl1 : a,b,c,c,d,e and Tbl2 : b,c
Then write an anonymous block as :
declare
b_cnt number(4) := 0;
a_cnt number(4) := 0;
begin
for i in (select distinct col from test)
loop
select count(*) into b_cnt from test1 where col=i.col;
if (b_cnt = 0) then
dbms_output.put_line(i.col);
else
select count(*) into a_cnt from test where col=i.col;
for j in 1..(a_cnt-b_cnt)
loop
dbms_output.put_line(i.col);
end loop;
end if;
end loop;
end;
/
Then the result is as follows:
a
c
c
d
e
Hope this is what you want.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
