3 Replies Latest reply: Jun 12, 2010 4:21 PM by Solomon Yakobson RSS

    count records from nested table

    778962
      Hello, I'm beginner of PL/SQL and a have a problem with Nested Table:


      I need to know, how to count rows from my nested table

      I have a colum, who is nested table (collections) of my objects, I want in one select query get all records where count is for example 2:

      select * from table_with_nested where nested_table.COUNT = 4;

      when I use function in PL/SQL and I have nested table as variable function of collection 'COUNT' work, but in select query I can't use this function of collection
        • 2. Re: count records from nested table
          Gurram
          Try this...

          SQL> desc paramobject;
          Name Null? Type
          ----------------------------------------- -------- ----------------------------
          PARAM_DESCRIPTION VARCHAR2(10)
          PARAM_VAL VARCHAR2(10)

          SQL> desc type2;
          Name Null? Type
          ----------------------------------------- -------- ----------------------------
          COL1 NUMBER
          COL2 PARAMOBJECT

          SQL> desc t1
          Name Null? Type
          ----------------------------------------- -------- ----------------------------
          A NUMBER
          B TYPE2

          insert into t1 values (1,type2(1,paramobject('siva','siva')));

          1 row created.

          SQL> commit;

          Commit complete.

          1* select a.b.col2 from t1 a
          SQL> /

          B.COL2(PARAM_DESCRIPTION, PARAM_VAL)
          --------------------------------------------------------------------------------
          PARAMOBJECT('siva', 'siva')

          SQL> select count(a.b.col2) from t1 a;

          COUNT(A.B.COL2)
          ---------------
          1
          • 3. Re: count records from nested table
            Solomon Yakobson
            user12285538 wrote:

            I have a colum, who is nested table (collections) of my objects, I want in one select query get all records where count is for example 2:
            Use CARDINALITY
            SQL> create or replace
              2    type type1
              3    as object(
              4              ename varchar2(20),
              5              sal   number
              6             )
              7  /
            
            Type created.
            
            SQL> create or replace
              2    type type2
              3    as table of type1
              4  /
            
            Type created.
            
            SQL> create table tbl1(
              2                    deptno number,
              3                    elist  type2
              4                   )
              5  nested table elist store as elist_tbl
              6  /
            
            Table created.
            
            SQL> insert
              2    into tbl1
              3    select  deptno,
              4            cast(collect(type1(ename,sal)) as type2)
              5      from  emp
              6      group by deptno
              7  /
            
            3 rows created.
            
            SQL> select * from tbl1
              2  /
            
                DEPTNO
            ----------
            ELIST(ENAME, SAL)
            ------------------------------------------------------------------------------------------------------------------------------------
                    10
            TYPE2(TYPE1('CLARK', 2450), TYPE1('KING', 5000), TYPE1('MILLER', 1300))
            
                    20
            TYPE2(TYPE1('SMITH', 800), TYPE1('FORD', 3000), TYPE1('ADAMS', 1100), TYPE1('SCOTT', 3000), TYPE1('JONES', 2975))
            
                    30
            TYPE2(TYPE1('ALLEN', 1600), TYPE1('BLAKE', 2850), TYPE1('MARTIN', 1250), TYPE1('TURNER', 1500), TYPE1('JAMES', 950), TYPE1('WARD', 1
            250))
            
                DEPTNO
            ----------
            ELIST(ENAME, SAL)
            ------------------------------------------------------------------------------------------------------------------------------------
            
            
            SQL> select  *
              2    from  tbl1
              3    where cardinality(elist) = 3
              4  /
            
                DEPTNO
            ----------
            ELIST(ENAME, SAL)
            ------------------------------------------------------------------------------------------------------------------------------------
                    10
            TYPE2(TYPE1('CLARK', 2450), TYPE1('KING', 5000), TYPE1('MILLER', 1300))
            
            
            SQL> 
            SY.