I have a table of keywords
select * from keywords;
id kw
-- --
1 foo
1 bar
2 foo
and a query that will select rows from a master table based on those keywords.
select id, stuff from assets
where id in (select unique id from keywords where kw = 'foo');
id stuff
-- -----
1 ...
2 ...
How can I turn the subquery into a function? i.e. I would like a function to return a set of values that can be used by an IN clause.
select id, stuff from assets
where id in HAS_KEYWORD('foo');
Table names differ a bit but the solution works
create table tab (
key number,
val number
);
create table foe(
col1 number,
col2 varchar2(3)
);
insert into tab values (0,3);
insert into tab values (1,4);
insert into tab values (2,5);
insert into foe values (3,'YES');
insert into foe values (4,'YES');
insert into foe values (5,'NO');
create type t_return is table of number;
/
create or replace function fnc(str varchar2)
return t_return pipelined is
begin
for rec in (select col1 from foe where col2 = str) loop
pipe row(rec.col1);
end loop;
end;
/
select * from tab where val in (select * from table(fnc('YES')))
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments