Multicolumn PI?

Database
Highlighted
KN
Enthusiast

Multicolumn PI?

If i look at DBC.INDICES view for Multi column PI table i thought with it should be available as 

db tb  columnname    Indetype

xx  yy   col1 , col 2     P

 

but i still find 

db tb  columnname    Indetype

xx  yy   col1                P

xx yy   col2                 P

 

We are in 14.10 and trying to find out if there is any metadata  table available that show the composite columns as it is?

 

Else 

 

How can i make it to the below

 

DB TB  Columnname Indextype

xx yy    col1,col2          P

 

 

2 REPLIES 2
Ambassador

Re: Multicolumn PI?

Hi,

 

Try the following, using recursive processing.

 WITH RECURSIVE All_Columns
(DatabaseName, TableName, ColumnList, IndexNumber,IndexType, depth ) 
AS 
(SELECT DatabaseName,  TableName,
        CAST(ColumnName AS VARCHAR(1000)),
        IndexNumber,
        IndexType,
        columnposition
FROM DBC.IndicesV
WHERE DatabaseName = 'dbc'
  and columnposition = 1
UNION ALL
SELECT All_Columns.DatabaseName,
       All_Columns.TableName,
      TRIM(All_Columns.ColumnList)||','||TRIM(IDX.ColumnName) 
                                      AS ColumnList,
     IDX.IndexNumber,
     IDX.IndexType,
     IDX.ColumnPosition
FROM All_Columns INNER JOIN DBC.IndicesV AS IDX
ON All_Columns.DatabaseName = IDX.DatabaseName 
AND All_Columns.IndexNumber = IDX.IndexNumber
AND All_Columns.TableName = IDX.TableName
and (all_columns.depth +1) = idx.columnposition
AND All_Columns.depth < 65 )
SELECT DatabaseName, TableName,IndexType, ColumnList
FROM All_Columns
qualify depth = max(depth) over(partition by DatabaseName, TableName,IndexNumber)
ORDER BY 1,2,3 ;

Change the original WHERE clause to target the required database etc.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Multicolumn PI?

XMLAgg seems easier here :)

 

  select DatabaseName
       , TableName
       , trim(trailing ',' from cast(XMLAgg(ColumnName || ',' order by ColumnPosition asc) as varchar(4000))) as Columns_PI
    from DBC.IndicesV
   where DatabaseName = 'DBC'
     and IndexType    = 'P'
group by DatabaseName
       , TableName;