访问 varray 列中的第二个元素 [英] Accessing 2th element in varray column

问题描述

假设有一个带有 varray 列的表，定义如下:

创建或替换 TYPE VARRAY_NUMBER_LIST AS VARRAY(15) OF NUMBER;

现在，我正在尝试选择表中每个 varray 列的第一个元素.它工作正常:

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

它返回如下输出:

214422

当我尝试使用此 SQL 获取每个 varray 列的第二个元素时出现问题:

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

在这种情况下，所有输出行都返回 null.如果我忘记了什么或让我感到困惑，请告诉我.

解决方案

您需要选择第 1 行和第 2 行，然后找出过滤掉不需要的前面行的方法 - 一种方法是使用带有 的聚合CASE 语句只匹配第二行:

SQL 小提琴

Oracle 11g R2 架构设置:

CREATE TABLE mytable ( myvarraycolumn ) AS从 DUAL UNION ALL 中选择 SYS.ODCINUMBERLIST( 1, 2, 3 )从 DUAL 中选择 SYS.ODCINUMBERLIST( 4, 5, 6 );

查询 1:

SELECT (SELECT MAX(CASE ROWNUM WHEN 2 THEN COLUMN_VALUE END)从表(t.myvarraycolumn)其中行数 <= 2) AS second_element从 mytable t

结果:

<代码>|SECOND_ELEMENT ||----------------||2 ||5 |

<块引用>

当我尝试使用此 SQL 获取每个 varray 列的第二个元素时出现问题:

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

在这种情况下，所有输出行都返回 null.如果我忘记了什么或让我感到困惑，请告诉我.

它不起作用，因为:对于相关内部查询中的第一行，ROWNUM 是 1 而您的过滤器是 WHERE ROWNUM = 2 那么这会减少到 WHERE 1=2 并且过滤器不匹配并且该行被丢弃.然后将针对 1 的 ROWNUM 测试后续行(因为前一行不再在输出中并且将没有行号)，这将再次失败测试并被丢弃.重复，令人恶心和所有行都未通过 WHERE 过滤器并被丢弃.

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:

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.

这篇关于访问 varray 列中的第二个元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！