获得NOKIA 系统中小区级别的信道配置

bbf3315

以下查询就可以获得NOKIA 系统中小区级别的信道配置，以供大家分享：
select substr(ltrim(bsc.name),1,3)
region

,bsc.object_instance
bsc_id

,bsc.name
bsc_name

,C_BTS.INT_ID
INT_ID

,bcf.object_instance
bcf_id

,c_bts.segment_id
seg_id

,c_bts.segment_name
seg_name

,c_bts.cell_id
cell_id

,BTS.object_instance
bts_id

,c_bts.nsei
NSEI

,decode(c_bts.GPRS_ENABLED,0,'0',1,'1','0')
GENA

,decode(c_bts.EGPRS_ENABLED,0,'0',1,'1','0')
EGENA

,DEFAULT_GPRS_CAPACITY
CDEF

,MAX_GPRS_CAPACITY
CMAX

,decode(BTS_IS_HOPPING,0,'N',1,'BB',2,'RF','NotDefined')
HOP

,decode(CS3_CS4_ENABLED,0,'N',1,'Y','NotDefined')
CS34Enabled
/*
OSS4 KPI */

,count(unique(trx.int_id))
trxnum
--
,sum(decode(gprs_enabled_trx,1,1,0))
gtrxnum

,sum(decode(ch.ch_0_type,0,1,2,1,0)

+ decode(ch.ch_1_type,0,1,2,1,0)

+ decode(ch.ch_2_type,0,1,2,1,0)


+ decode(ch.ch_3_type,0,1,2,1,0)

+ decode(ch.ch_4_type,0,1,2,1,0)

+ decode(ch.ch_5_type,0,1,2,1,0)

+ decode(ch.ch_6_type,0,1,2,1,0)

+ decode(ch.ch_7_type,0,1,2,1,0))
tchch

,sum(decode(gprs_enabled_trx,1,decode(ch.ch_0_type,0,1,2,1,0)

+ decode(ch.ch_1_type,0,1,2,1,0)

+ decode(ch.ch_2_type,0,1,2,1,0)

+ decode(ch.ch_3_type,0,1,2,1,0)

+ decode(ch.ch_4_type,0,1,2,1,0)

+ decode(ch.ch_5_type,0,1,2,1,0)

+ decode(ch.ch_6_type,0,1,2,1,0)

+ decode(ch.ch_7_type,0,1,2,1,0),0))
gtchch

,sum(decode(gprs_enabled_trx,1,

decode(ch.ch_0_type,0,1,2,1,0)

+ decode(ch.ch_1_type,0,1,2,1,0)

+ decode(ch.ch_2_type,0,1,2,1,0)

+ decode(ch.ch_3_type,0,1,2,1,0)

+ decode(ch.ch_4_type,0,1,2,1,0)

+ decode(ch.ch_5_type,0,1,2,1,0)

+ decode(ch.ch_6_type,0,1,2,1,0)

+ decode(ch.ch_7_type,0,1,2,1,0),0))*DEdicated_GPRS_CAPACITY/100 GP_CDED

,sum(decode(gprs_enabled_trx,1,

decode(ch.ch_0_type,0,1,2,1,0)

+ decode(ch.ch_1_type,0,1,2,1,0)

+ decode(ch.ch_2_type,0,1,2,1,0)

+ decode(ch.ch_3_type,0,1,2,1,0)

+ decode(ch.ch_4_type,0,1,2,1,0)

+ decode(ch.ch_5_type,0,1,2,1,0)

+ decode(ch.ch_6_type,0,1,2,1,0)

+ decode(ch.ch_7_type,0,1,2,1,0),0))*DEFAULT_GPRS_CAPACITY/100 GP_CDEF

from

objects bsc,

objects bcf,

objects bts,

objects trx,

c_bts,

c_trx ch

where


bts.object_class=4 and

bsc.object_class=3 and

bcf.object_class=27 and

trx.object_class=24 and

bts.parent_int_id=bcf.int_id and

bcf.parent_int_id=bsc.int_id and

trx.parent_int_id=bts.int_id and

c_bts.int_id=bts.int_id and

ch.int_id=trx.int_id and

c_bts.conf_name='<ACTUAL>' and


/* bsc.name not like 'For%'
*/


bsc.name like '&reg%'
/* BSC 0 is the external BSC,not internal BSC */

group by

substr(ltrim(bsc.name),1,3),

bsc.object_instance,

bsc.name,

bsc.int_id,

bcf.object_instance,

LA_ID_LAC,

c_bts.cell_id,

c_bts.int_id,


c_bts.segment_id,

c_bts.segment_name,

BTS.object_instance ,

c_bts.nsei,

decode(c_bts.GPRS_ENABLED,0,'0',1,'1','0'),

decode(c_bts.EGPRS_ENABLED,0,'0',1,'1','0'),

DEDICATED_GPRS_CAPACITY,

DEFAULT_GPRS_CAPACITY,

MAX_GPRS_CAPACITY,

decode(BTS_IS_HOPPING,0,'N',1,'BB',2,'RF','NotDefined')

,decode(CS3_CS4_ENABLED,0,'N',1,'Y','NotDefined')