开发者

Extract Table name and Count

I do have a table like this shown at input.

In this table i want to extract only this Table Space Name and Count

*******************************************************************************************************************************
Output
*******************************************************************************************************************************

TABLESPACE_NAME               Nr of parts 

ACCUM_JOURNAL_ENTRIES_DATA  24
ACCOUNT_BALANCES_DATA           24
*******************************************************************************************************************************
Input
*******************************************************************************************************************************


DWSADM               ACCUMULATOR_JOURNAL_ENTRIES    M200905_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20090601000
DWSADM                                              M200906_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20090701000

TABLE_OWNER          TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                HIGH_VALUE
-------------------- ------------------------------ ------------------------------ ------------------------------ ---------------
DWSADM               ACCUMULATOR_JOURNAL_ENTRIES    M200907_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20090801000
DWSADM                                              M200908_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20090901000
DWSADM                                              M200909_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20091001000
DWSADM                                              M200910_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20091101000
DWSADM                                              M200911_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20091201000
DWSADM    开发者_Python百科                                          M200912_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20100101000
DWSADM                                              M201001_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20100201000
DWSADM                                              M201002_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20100301000
DWSADM                                              M201003_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20100401000
DWSADM                                              M201004_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20100501000
DWSADM                                              M201005_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20100601000
DWSADM                                              M201006_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20100701000
DWSADM                                              M201007_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20100801000
DWSADM                                              M201008_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20100901000
DWSADM                                              M201009_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20101001000
DWSADM                                              M201010_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20101101000
DWSADM                                              M201011_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20101201000
DWSADM                                              M201012_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20110101000
DWSADM                                              M201101_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20110201000
DWSADM                                              M201102_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20110301000
DWSADM                                              M201103_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20110401000
DWSADM                                              M201104_ACCUM_JOU_ENT          ACCUM_JOURNAL_ENTRIES_DATA     20110501000
                     ****************************** ------------------------------
                     Nr of parts                                                24


DWSADM               ACC_ACCOUNT_BALANCES           M200905_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20090601000
DWSADM                                              M200906_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20090701000
DWSADM                                              M200907_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20090801000
DWSADM                                              M200908_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20090901000
DWSADM                                              M200909_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20091001000
DWSADM                                              M200910_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20091101000
DWSADM                                              M200911_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20091201000
DWSADM                                              M200912_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20100101000
DWSADM                                              M201001_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20100201000
DWSADM                                              M201002_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20100301000
DWSADM                                              M201003_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20100401000

TABLE_OWNER          TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                HIGH_VALUE
-------------------- ------------------------------ ------------------------------ ------------------------------ ---------------
DWSADM               ACC_ACCOUNT_BALANCES           M201004_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20100501000
DWSADM                                              M201005_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20100601000
DWSADM                                              M201006_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20100701000
DWSADM                                              M201007_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20100801000
DWSADM                                              M201008_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20100901000
DWSADM                                              M201009_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20101001000
DWSADM                                              M201010_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20101101000
DWSADM                                              M201011_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20101201000
DWSADM                                              M201012_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20110101000
DWSADM                                              M201101_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20110201000
DWSADM                                              M201102_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20110301000
DWSADM                                              M201103_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20110401000
DWSADM                                              M201104_ACC_ACC_BAL            ACCOUNT_BALANCES_DATA          20110501000
                     ****************************** ------------------------------
                     Nr of parts                                                24


your question is not very clear, although it seems a simple GROUP BY would work here:

SELECT tablespace_name, COUNT(*) nb_of_parts
  FROM your_table
 GROUP BY tablespace_name
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜