Search code examples
oracleplsqlnested-tablesql-types

Count rows in own type such as type as table or type as object

My own type

CREATE OR REPLACE TYPE chain_t AS TABLE OF NUMBER(11);

select column as this type in select statement

select 
x.id, 
cast(multiset(select parent_id from table(x.parents)) as chain_t) as chain
from
xxxx x

if x.parents have values i have next result, for example:

chain_t(22, 44)

or if dont have 

chain_t(null)

How i can count rows in chain?? For example in first result 2 rows in second result 0 rows

Solution

  • Use the CARDINALITY() function. Find out more.

    select id, chain, cardinality(chain) 
from (
   select 
           x.id, 
           cast(multiset(select parent_id from x) as chain_t) as chain
    from x
)
/

    Here is a SQL Fiddle based on this simplified version of your posted code.