主页 / dba / 问题 / 207415
Accepted
estonolose
Asked: 2018-05-22 11:34:39 +0800 CST

With Query 返回多行

  • 772

在此查询中，我想显示表的分区和子分区的列。如果NOM_TABLA查询仅获取一个表，则可以正常工作，但是当Select提供多个表格时，它就无法工作。

with nom_tabla as 
(
  select table_name from dba_tables 
  where table_name like 'TE%'
), 
col_part as 
(
  SELECT listagg(column_name, ', ' ) 
    within group (order by column_position) PART_POR 
  FROM dba_part_key_columns WHERE NAME in 
  (
    select * 
    from nom_tabla
  ) 
  GROUP BY name 
  ORDER BY name
), 
col_subpart as 
(
  SELECT listagg(column_name, ', ' ) 
    within group (order by column_position) PART_POR 
  FROM dba_subpart_key_columns 
  WHERE NAME IN 
  (
    select * from nom_tabla
  ) 
  GROUP BY name 
  ORDER BY name
)
select  
  t.OWNER PROPIETARIO, 
  t.TABLE_NAME as TABLA, 
  ' - ' as PARTICION, 
  NVL (pt.PARTITIONING_TYPE,' - ') AS TIPO_PART, 
  ' - ' as SUBPARTICION, 
  NVL(pt.SUBPARTITIONING_TYPE, ' - ') AS TIPO_SUBPART,
  NVL(t.tablespace_name,' - ') as TS, 
  NVL((select * from col_part),'-') , 
  NVL((select * from col_subpart),'-') 
  From dba_tables t, dba_part_tables pt 
  where   t.owner not in
  ('SYS','SYSTEM','DBSNMP','SYSCAT','WMSYS','MDSYS','XDB','ORDDATA')
  and     t.owner=pt.owner(+) 
  and t.table_name=pt.table_name(+)
  and t.TABLE_NAME in (select * from nom_tabla);

我认为问题出在查询的这一部分，因为它返回了多个记录：

, NVL((select * from col_part),'-') , NVL((select * from col_subpart),'-')

我不知道如何避免这个问题。

oracle

1 个回答

  1. Best Answer

    当然。

    col_part as 
(
  SELECT listagg(column_name, ', ' ) 
    within group (order by column_position) PART_POR 
  FROM dba_part_key_columns WHERE NAME in 
  (
    select * 
    from nom_tabla
  ) 
  GROUP BY name 
  ORDER BY name
)

    如果nom_table有多于 1 行，则子查询也返回多于 1 行。

    这有效：

       select
      t.owner as propietario,
      t.table_name as tabla,
      ' - ' as particion,
      nvl(pt.partitioning_type, ' - ') as tipo_part,
      ' - ' as subparticion,
      nvl(pt.subpartitioning_type, ' - ') as tipo_subpart,
      nvl(t.tablespace_name, ' - ') as ts,
      nvl(col_part.part_por, '-'),
      nvl(col_subpart.part_por, '-')
    from
      dba_tables t
      left join dba_part_tables pt on (t.owner = pt.owner and t.table_name = pt.table_name)
      left join (select owner, name, listagg(column_name, ', ') within group (order by column_position) part_por from dba_part_key_columns group by owner, name order by owner, name) col_part on (t.owner = col_part.owner and t.table_name = col_part.name)
      left join (select owner, name, listagg(column_name, ', ') within group (order by column_position) part_por from dba_subpart_key_columns group by owner, name order by owner, name) col_subpart on (t.owner = col_subpart.owner and t.table_name = col_subpart.name)
    where
      t.owner not in ('SYS','SYSTEM','DBSNMP','SYSCAT','WMSYS','MDSYS','XDB','ORDDATA')
      and t.table_name like 'TE%'
    ;
    • 0

相关问题