Accessing 2th element in varray column

Question

Let's say a have a table with a varray column, defined as follow:

create or replace TYPE VARRAY_NUMBER_LIST AS VARRAY(15) OF NUMBER;

Now, I'm trying to select the first element of each varray column of my table. It works fine:

select (select * from table(myvarraycolumn) where rownum = 1) from mytable cc

It is returning an output like:

My issue occurs when I try to get the second element of each varray column with this SQL:

select (select * from table(myvarraycolumn) where rownum = 2) from mytable cc

In this case, all output lines are returning null. Please, let me know if I'm forgetting something or making some confusion.

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

You need to select rows 1 and 2 and then work out a way to filter out the unwanted preceding rows - one way is to use aggregation with a CASE statement to only match the second row:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE mytable ( myvarraycolumn ) AS
  SELECT SYS.ODCINUMBERLIST( 1, 2, 3 ) FROM DUAL UNION ALL
  SELECT SYS.ODCINUMBERLIST( 4, 5, 6 ) FROM DUAL;

Query 1:

SELECT (
         SELECT MAX( CASE ROWNUM WHEN 2 THEN COLUMN_VALUE END )
         FROM   TABLE( t.myvarraycolumn )
         WHERE  ROWNUM <= 2
       ) AS second_element
FROM   mytable t

Results:

| SECOND_ELEMENT |
|----------------|
|              2 |
|              5 |

My issue occurs when I try to get the second element of each varray column with this SQL:
select (select * from table(myvarraycolumn) where rownum = 2) from mytable cc 
In this case, all output lines are returning null. Please, let me know if I'm forgetting something or making some confusion.

It is not working because: for the first row in the correlated inner query, ROWNUM is 1 and your filter is WHERE ROWNUM = 2 then this reduces to WHERE 1=2 and the filter is not matched and the row is discarded. The subsequent row will then be tested against a ROWNUM of 1 (since the previous row is no longer in the output and will not have a row number), which will again fail the test and be discarded. Repeat, ad nauseum and all rows fail the WHERE filter and are discarded.

Accessing 2th element in varray column

1 Answers1