CODEWITHSUNDEEP
sqloracle-databasenumbers
Filip
Filip

Reputation: 1097

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?

Upvotes: 3

Views: 3684

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

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

should do it.

Upvotes: 3

Related Questions