hierarchy (recursive) query

Database

hierarchy (recursive) query

Hi,

I have the below hirerchy -

                        D1

         D2           D3       D4

      D5  D6     D7 D8   D9 D10

 T1 T2 T3 T4  T5 T6 T7 T8 T9 T10 T11

 

My requirement is to find out currentperm of all the tables but in condition I can only give databasename is D1. Below is the query which is working at one level but not till the end.-

locking row for access

SELECT

a.DatabaseName AS DatabaseName,

a.TableName AS ObjectName,

a.tablekind AS Tablekind,

CAST (a.CreateTimeStamp AS DATE) AS Create_Date,

sum(b.currentperm)/1024/1024/1024 AS ObjectSize

FROM       dbc.TABLES a left outer join dbc.tablesize b

 on       

            a.databasename=b.databasename

            and a.tablename=b.tablename

WHERE

a.databasename in (

sel a.databasename

from dbc.databases a inner join dbc.databases b

on a.databasename=b.databasename

where a.ownername in ( 'D1')

)

group   by 1,2,3,4

order by 4 desc;

1 REPLY

Re: hierarchy (recursive) query

Here is the solution -

locking row for access

SELECT DATABASENAME, 

TABLENAME, 

SUM(CURRENTPERM)/1024/1024/1024

FROM DBC.TABLESIZE 

where databasename in (SELECT DISTINCT TRIM(child) AS DB

FROM   dbc.children A

WHERE               (parent = TRIM('D1') 

              --  OR         TRIM(child) = TRIM('audata00_vha')

))

 GROUP BY 1, 2

 order by 3 desc