Select lines where nested table column meets a condition

Question

v is defined as follows: create or replace type v is table of number and emp is a table which contains a column of type v.

I want to select the lines where v.count is 3, but I will get a compilation error. Is it because v.count is PL/SQL code?

I tried putting the code inside an anonymous block but it still didn't work.

Is using cursors the only solution?

 SELECT *
 FROM emp
 WHERE V.COUNT = 3;

Thanks.

Answer 1

I think you're looking for cardinality():

CARDINALITY returns the number of elements in a nested table. The return type is NUMBER. If the nested table is empty, or is a null collection, then CARDINALITY returns NULL.

So you can do:

SELECT *
FROM emp
WHERE cardinality(V) = 3;

Quick demo:

create or replace type v is table of number
/

create table emp (id number, v v)
nested table v store as v_tab;

insert into emp (id, v) values (1, v(1));
insert into emp (id, v) values (2, v(1,2));
insert into emp (id, v) values (3, v(1,2,3));
insert into emp (id, v) values (4, v(1,2,3,4));

column v format a30
set feedback 1

SELECT *
FROM emp
WHERE cardinality(V) = 3;

        ID V                             
---------- ------------------------------
         3 V(1, 2, 3)                    

1 row selected.