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.

Answer 1

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.