SQL嵌套子查询引用祖父母列

小编典典

SQL嵌套子查询引用祖父母列

sql

我对报告有一个特别复杂的查询。它从视图中选择了几列，并且必须通过聚合级联多个字段来构建一列。为了进一步使事情复杂化，即使实际上有0个，连接也必须包含3个字段（连接以逗号分隔，因此仍然会注意到空字段）。

我们正在使用Oracle 11.1.0.7.0。

为了提供向后兼容性（不是必需的），我们使用xmlagg函数执行连接，我相信自Oracle 8或9以来就已经存在。

这个例子将被简化，但是我觉得提供了足够的信息。换句话说，请不要专注于规范化表结构，这严格是一个示例。

person_view
-----------
name
phone
address
position_id


position_table
--------------
position_id
position_title

因此，我们目前拥有的查询（我承认不是SQL专家）类似于：

select
  name,
  phone,
  address,
  (select
     xmlagg(xmlelement(e, position_title || ',')).extract('//text()')
   from
     (select
        position_title
      from
        position_table
      where
        position_table.position_id = person_view.position_id and 
        rownum <= 3
      union all select '' from dual
      union all select '' from dual
      union all select '' from dual
     )
   where
     rownum <= 3
  )
from
  person_view

我的实际错误是，看起来确保至少3行输入的子查询无法引用祖父母查询来确定person_view.position_id。

我收到ORA-00904：“ PERSON_VIEW”。“ POSITION_ID”：无效的标识符

性能并不是一个大问题，因为这是不会定期运行的报告，但是我需要找到一种解决方案，以将数据与绝对3列数据进行聚合。非常感谢您提供任何有助于重写查询或允许子查询访问相关祖父母列的指导。

阅读 348

2021-04-22

共1个答案

小编典典

这是Oracle SQL的一个局限性：您不能从子查询中引用超过1级的父查询元素。

在这种情况下，我将使用一个函数：

CREATE OR REPLACE FUNCTION get_title(p_position_id NUMBER) RETURN VARCHAR2 IS
   l_result       LONG;
   l_position_num NUMBER := 0;
BEGIN
   FOR cc IN (SELECT position_title
                FROM position_table
               WHERE position_table.position_id = p_position_id
                 AND rownum <= 3) LOOP
      l_result       := cc.position_title || ',';
      l_position_num := l_position_num + 1;
   END LOOP;
   RETURN l_result || rpad(',', 3 - l_position_num, ',');
END;

您的查询将如下所示：

select
  name,
  phone,
  address,
  get_title(p.position_id) title
from person_view p

2021-04-22