开发者

Use table of number as filter in where clause

开发者 https://www.devze.com 2023-01-27 05:34 出处:网络
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)

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_开发者_JAVA技巧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?


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

should do it.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号