Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Count nested table column in SQL

JackKSep 22 2021 — edited Sep 22 2021

Hi All,
I am using Oracle 19c.

create or replace type t_numbers is table of number;

create table test_tab (
  id    number(22),
  nums  t_numbers
) nested table nums store as nt_tt_nums;

INSERT INTO test_tab VALUES (1, t_numbers(1,2,3,4));
INSERT INTO test_tab VALUES (2, t_numbers(1,2,3,4,5));
INSERT INTO test_tab VALUES (3, t_numbers(1,2,3,4,5,6));

How may I count elements in nums column?
Below statement does not work because of ORA-00904: "NUMS"."COUNT": invalid identifier

SELECT id, nums.count AS has_items
 FROM test_tab;

Best regards,
Jacek

Cleaning:

drop table test_tab;
drop type t_numbers;
This post has been answered by mathguy on Sep 22 2021
Jump to Answer
Comments
Post Details
Added on Sep 22 2021
26 comments
3,261 views