With Query return more than one line

oracle

In this query I want to display the columns of partition and subpartition of a table. It works fine if the nom_tabla query get only one table, but when the select give us more than one table it doesn't works.

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);

I think that the problem is in this section of the query because it return more than one record:

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

I don't know how to avoid this problem.

Best Answer

Of course.

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
)

If nom_table has more than 1 row, the subquery returns more than 1 row as well.

This works:

   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%'
    ;