Filip Filip - 1 month ago 5
SQL Question

Use table of number as filter in where clause

I want to use a Table of Number as a filter in a select statement.

This is the Table of Numbers:

CREATE OR REPLACE TYPE AUTOCONTROLE2.ListNumbers AS TABLE OF NUMBER(10)


This is a little test sql:

declare
testvar number;
ActiviteitSAC autocontrole2.ListNumbers := autocontrole2.ListNumbers(189449, 189456, 189473);
begin
select count(O.pap_id) into testvar
from pap_operator O
where O.PAP_OPERATOR_ID in (ActiviteitSAC(1), ActiviteitSAC(2), ActiviteitSAC(3));
end;


I want to replace the ActiviteitSAC(1), ActiviteitSAC(2), ActiviteitSAC(3) by something like
"select * from ActiviteitSAC".

Any ideas?

Answer
SELECT count( o.pap_id) 
  INTO testvar
  FROM pap_operator o
 WHERE o.pap_operator_id IN (SELECT * FROM TABLE(ActiveiteitSAC) );

should do it.

Comments