I have a dilema.
I have I want to add tags to my records in a child table. When selecting the parent records I want to find all tags they all hold in common.
Here is an example with the tags listagg'ed together for readability:
id stuff tags
258 PDT fortran:function:java:package
259 HDH java:package:procedure
So if I select these 2 records I would want java and package tags to be selected.
However the number of records I select is unknown so I cant just write an intersect for each record.
I have added a little table creation script if that helps.
clear screen
begin
for r_tab in
( select table_name
from user_tables
where table_name in ('XXX_PG_STUFF','XXX_PG_TAGS','XXX_PG_STUFF_TAGS')
)
loop
execute immediate 'drop table '||r_tab.table_name||' cascade constraints purge';
end loop;
end;
/
CREATE TABLE xxx_pg_stuff
(id integer not null
,stuff varchar2(3)
,constraint stuff_pk primary key(id)
);
CREATE TABLE xxx_pg_tags
(tag_id integer not null
,tag varchar2(20)
,constraint tag_pk primary key ( tag_id )
);
create table XXX_PG_STUFF_TAGS
(stuff_id integer
,tag_id integer
,constraint stuff_tag_pk primary key(stuff_id,tag_id)
);
-- set SERVEROUTPUT on
declare
i simple_integer:=0;
j simple_integer:=0;
l_tags_cnt simple_integer:=0;
l_tag_loc simple_integer:=0;
type tags_tab_type is table of varchar2(20) index by binary_integer;
t_tags tags_tab_type;
t_tags_used tags_tab_type;
l_tag_used varchar2(20);
begin
for i in 1..1000 loop
insert into xxx_pg_stuff
(id
,stuff
)
values
(i
,dbms_random.string('U',3)
);
-- dbms_output.put_line('inserted');
end loop;
dbms_output.put_line('done l1 ');
insert into xxx_pg_tags
(taG_ID, tag)
with tags as
( select 'sql' tag
from dual
union all
select 'plsql'
from dual
union all
select 'fortran'
from dual
union all
select 'x#'
from dual
union all
select 'java'
from dual
union all
select 'package'
from dual
union all
select 'function'
from dual
union all
select 'procedure'
from dual
union all
select 'p1 incident'
from dual
union all
select 'we love tags'
from dual
)
select ROWNUM,tag
from ( tags );
commit;
i:=0;
for r in (select * from xxx_pg_tags ) loop
i:=i+1;
t_tags(i):=r.tag;
end loop;
for i in t_tags.first..t_tags.last loop
dbms_output.put_line('tag '||i||' = '||t_tags(i) );
end loop;
for r_stuff in
( select *
from xxx_pg_stuff
)
loop
l_tags_cnt:=floor(dbms_random.value(1,5) );
t_tags_used.delete;
-- t_tags_used:=new t_tags_used();
for i in 1..l_tags_cnt loop
j:=0;
loop
j:=j+1;
l_tag_loc:=floor(dbms_random.value(1,t_tags.last + 1 - 0.000000000001 ) );
dbms_output.put_line('try and use '||l_tag_loc);
dbms_output.put_line('it is '||t_tags(l_tag_loc) );
if NOT t_tags_used.exists(l_tag_loc) then
dbms_output.put_line('using it ');
-- t_tags_used.extend();
t_tags_used(l_tag_loc):=t_tags(l_tag_loc);
exit;
end if;
if j > 10 then
raise_application_error(-20999,'too much');
end if;
end loop;
dbms_output.put_line('insert into XXX_PG_STUFF_TAGS '||t_tags(l_tag_loc) );
insert into XXX_PG_STUFF_TAGS
(stuff_id
,tag_id
)
select r_stuff.id, tag_id
from xxx_pg_tags
where tag = t_tags(l_tag_loc);
end loop; -- i
end loop;
commit;
end;
/