How can I select a particular row from a nested table?

Question

I have a table that looks like this:

 CREATE
     OR
REPLACE
   TYPE subaccount_nt 
     IS TABLE OF VARCHAR2(30);

 CREATE
   TABLE my_table
       ( contract_id   NUMBER(38,0)
       , subaccount    SUBACCOUNT_NT );

Here's some sample data:

   100          [1000, 1, 2]
   200          [2000, NULL, 999]
   300          [3000]

How can I write a query to return the the third row from the nested table if the 3rd row exists? Here's the output I'm trying to get:

   100          1
   200          NULL
   300          NULL

Haeving never worked with nested tables before, I'm finding it quite hard to forumate my query. Thanks.

"third row from the nested table" . you have a problem as nested tables get stored in no particular order (just like regular tables). so the "third row" may be any element and not necessarily the third item you inserted. eg if you inserted ` insert into my_table values (200, SUBACCOUNT_NT(2000, NULL, 999));` 999 may or may not be the third row over time. only VARRAYs have a reliable ordering — DazzaL, Feb 12 '13 at 13:58

Vincent Malgrat · Accepted Answer · 2013-02-12T14:01:52.200

You can use analytics with a lateral join (unnesting of collection):

SQL> SELECT contract_id, CASE WHEN rn = 2 THEN val END val
  2    FROM (SELECT t.contract_id, column_value val,
  3                 row_number() over(PARTITION BY t.contract_id ORDER BY 1) rn,
  4                 COUNT(*) over (PARTITION BY t.contract_id) cnt
  5            FROM my_table t,
  6                 TABLE(t.subaccount))
  7   WHERE rn = 2 OR cnt = 1;

   CONTRACT_ID VAL
-------------- ---
           100 1
           200 
           300

This will not list rows that have an empty subaccount.

By the way the order is not guaranteed since the nested tables are stored as unordered sets of rows.

How can I select a particular row from a nested table?

1 Answers1