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:
2
1
4
4
2
2
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.
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:
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
| 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.