腳本.doc (30.42 KB)【資料名稱】：腳本

【資料作者】：11

【資料日期】：2011-3-1

【資料語(yǔ)言】：中文

【資料格式】：DOC

【資料目錄和簡(jiǎn)介】：

/*查詢?nèi)�網(wǎng)或單個(gè)小區(qū)(E)GPRS指標(biāo)
**/
Select
gprs.Sdatetime
,BSCNAME
,LAC
,NSEI
,CI
,SEG_ID
,BCF_ID
,BTS_ID
,BTSNAME
,decode(EGENA,1,'Y',0,'N') 是否開啟EDGE
,decode(GENA,1,'Y',0,'N') 是否開啟GPRS
,ERL 話務(wù)量
,erl_p_line 每線話務(wù)量
,DED_CHPDCH靜態(tài)信道
,AVE_GPRS_CHGPRS平均激活信道數(shù)
,GPRS_PEAK_CHGPRS最大激活信道數(shù)
,UL_G_TBF_Est上行G_TBF建立嘗試次數(shù)
,DL_G_TBF_Est下行G_TBF建立嘗試次數(shù)
,UL_E_TBF上行E_TBF建立嘗試次數(shù)
,DL_E_TBF下行E_TBF建立嘗試次數(shù)
,UL_TBF_SUCCESS_RATE 上行TBF建立成功率
,dL_TBF_SUCCESS_RATE 下行TBF建立成功率
,DL_CS2BLER 下行CS2誤碼率
,dl_high_code_rate下行高編碼比例
,DL_TBF_BLK 下行TBF擁塞率資源不足
,DL_TBF_BLK_NUM下行TBF擁塞數(shù)資源不足
,DL_TBF_REQ 下行TBF請(qǐng)求次數(shù)
,DL_TBF_NUM_1TSL下行復(fù)用度
,UL_TBF_BLK 上行TBF擁塞率資源不足
,UL_TBF_BLK_NUM上行TBF擁塞數(shù)資源不足
,UL_TBF_REQ 上行TBF請(qǐng)求次數(shù)
,UL_TBF_NUM_1TSL上行復(fù)用度
,MSL_6下行無(wú)線信道充足率
,CS_REJGPRS升級(jí)拒絕CS資源不足
,PS_REJGPRS升級(jí)拒絕PS資源不足
,PCU_REJ GPRS升級(jí)拒絕PCU不足
,UP_REQGPRS升級(jí)請(qǐng)求次數(shù)
,P_CH_REQ分組數(shù)據(jù)請(qǐng)求次數(shù)
,p_imm_ass包立即指配消息數(shù)
,P_IMM_ASS_REJ_MSG包立即指配拒絕消息數(shù)
,P_IMM_ASS_A_MSG確認(rèn)包立即指配消息數(shù)
,P_IMM_ASS_NA_MSG 非確包立即指配消息數(shù)
,IMM_ASSGN_SENT立即指配發(fā)送消息數(shù)
,IMM_ASSGN_REJ 立即指配拒絕消息數(shù)
,dl_mcs1_payload下行MCS1吞吐量
,dl_mcs2_payload下行MCS2吞吐量
,dl_mcs3_payload下行MCS3吞吐量
,dl_mcs4_payload下行MCS4吞吐量
,dl_mcs5_payload下行MCS5吞吐量
,dl_mcs6_payload下行MCS6吞吐量
,dl_mcs7_payload下行MCS7吞吐量
,dl_mcs8_payload下行MCS8吞吐量
,dl_mcs9_payload下行MCS9吞吐量
,UL_GPRS_PAYLOAD上行GPRS吞吐量
,DL_GPRS_PAYLOAD下行GPRS吞吐量
,ULEGPR_PAYLOAD上行EGPRS吞吐量
,DLEGPR_PAYLOAD下行EGPRS吞吐量
,EF_ACK_UL_TP_1TSL_TRF_234 上行EDGE單時(shí)隙吞吐量
,EF_ACK_DL_TP_1TSL_TRF_236 下行EDGE單時(shí)隙吞吐量

From
(
select
p.int_id int_id
,p.bts_int_id bts_int_id
,to_char(p.period_start_time,'yyyymmddhh24') Sdatetime
,o_bsc.name BSCname
,bts.la_id_lac LAC
,bts.nsei NSEI
,bts.cell_id CI
,bts.segment_id SEG_ID
,o_bcf.object_instance bcf_id
,o_bts.object_instance bts_id
,o_bts.name btsname
,bts.EGPRS_ENABLED EGENA
,bts.GPRS_ENABLED GENA
,round(decode(Sum(r.res_av_denom14),0,0,Sum(r.ave_busy_tch)/Sum(r.res_av_denom14)),2) erl
,decode(
sum(decode(res_av_denom2,0,-1,ave_avail_full_TCH/res_av_denom2)),0,-1,
round(decode(Sum(r.res_av_denom14),0,0,Sum(r.ave_busy_tch)/Sum(r.res_av_denom14))/
sum(decode(res_av_denom2,0,-1,ave_avail_full_TCH/res_av_denom2)),2)
) erl_p_line
,round(decode(Sum(r.ave_permanent_GPRS_ch_den),0,0,Sum(r.ave_permanent_GPRS_ch_sum)/Sum(r.ave_permanent_GPRS_ch_den)),2) ded_ch
,round(decode(Sum(r.ave_GPRS_channels_den),0,0,Sum(r.ave_GPRS_channels_sum)/Sum(r.ave_GPRS_channels_den)),2) AVE_GPRS_CH
,max(r.PEAK_GPRS_CHANNELS) GPRS_PEAK_CH
,round(decode(
sum(req_1_TSL_DL+req_2_TSL_DL+req_3_TSL_DL+req_4_TSL_DL+req_5_8_TSL_DL),0,0,
100*sum (NO_RADIO_RES_AVA_DL_TBF)/sum(req_1_TSL_DL+req_2_TSL_DL+req_3_TSL_DL+req_4_TSL_DL+req_5_8_TSL_DL)
)
,2) DL_TBF_BLK
,sum(NO_RADIO_RES_AVA_DL_TBF) dl_tbf_blk_num
,sum(req_1_TSL_DL+req_2_TSL_DL+req_3_TSL_DL+req_4_TSL_DL+req_5_8_TSL_DL) dl_tbf_req
,round(decode(
sum(p.aver_tbfs_per_tsl_dl_den),0,0,
sum(p.aver_tbfs_per_tsl_dl_sum)/(sum(p.aver_tbfs_per_tsl_dl_den)*100)
)
,2) dl_tbf_num_1tsl
,round(decode(
sum(req_1_TSL_UL+req_2_TSL_UL+req_3_TSL_UL+req_4_TSL_UL+req_5_8_TSL_UL),0,0,
100*sum (NO_RADIO_RES_AVA_UL_TBF)/sum(req_1_TSL_UL+req_2_TSL_UL+req_3_TSL_UL+req_4_TSL_UL+req_5_8_TSL_UL)
)
,2) UL_TBF_BLK
,sum(NO_RADIO_RES_AVA_UL_TBF) ul_tbf_blk_num
,sum(req_1_TSL_UL+req_2_TSL_UL+req_3_TSL_UL+req_4_TSL_UL+req_5_8_TSL_UL) ul_tbf_req
,round(decode(
sum(p.aver_tbfs_per_tsl_ul_den),0,0,
sum(p.aver_tbfs_per_tsl_ul_sum)/(sum(p.aver_tbfs_per_tsl_ul_den)*100)
)
,2) ul_tbf_num_1tsl
,round(decode(
sum (req_1_TSL_DL + 2 * req_2_TSL_DL + 3 * req_3_TSL_DL + 4 * req_4_TSL_DL),0,0,
100*sum (alloc_1_TSL_DL + 2 * alloc_2_TSL_DL+ 3 * alloc_3_TSL_DL+ 4 * alloc_4_TSL_DL)/
sum (req_1_TSL_DL + 2 * req_2_TSL_DL + 3 * req_3_TSL_DL + 4 * req_4_TSL_DL)
)
,2) msl_6/*1- blk33，無(wú)線信道充足率*/
,sum(p.PACKET_CH_REQ) p_ch_req/*via CCCH*/
,Sum(p.PACKET_IMMED_ASS_MSG) p_imm_ass
,Sum(p.PACKET_IMMED_ASS_REJ_MSG) p_imm_ass_rej_msg
,Sum(p.PACKET_IMMED_ASS_ACK_MSG) p_imm_ass_a_msg /* 反映AGCH buffer擁塞*/
,Sum(p.PACKET_IMMED_ASS_NACK_MSG)p_imm_ass_na_msg/* 反映AGCH buffer擁塞*/
,round(decode(
sum(p.NBR_OF_UL_TBF+ p.NBR_OF_DL_TBF- p.UL_TBF_Establishment_Failed - p.DL_TBF_Establishment_Failed- p.UL_EGPRS_TBF_REL_DUE_NO_RESP - p.DL_EGPRS_TBF_REL_DUE_NO_RESP-p.UL_TBF_REL_DUE_TO_FLUSH-p.DL_TBF_REL_DUE_TO_FLUSH- p.UL_TBF_REL_DUE_TO_SUSPEND-p.DL_TBF_REL_DUE_TO_SUSPEND),0,-1,
100-100*sum(p.NBR_OF_UL_TBF+ p.NBR_OF_DL_TBF- decode(AVE_DUR_UL_TBF_SUM,0,0,AVE_DUR_UL_TBF_DEN)
- decode(AVE_DUR_DL_TBF_SUM,0,0,AVE_DUR_DL_TBF_DEN)
- p.UL_TBF_Establishment_Failed - p.DL_TBF_Establishment_Failed- p.UL_EGPRS_TBF_REL_DUE_NO_RESP - p.DL_EGPRS_TBF_REL_DUE_NO_RESP-p.UL_TBF_REL_DUE_TO_FLUSH-p.DL_TBF_REL_DUE_TO_FLUSH- p.UL_TBF_REL_DUE_TO_SUSPEND-p.DL_TBF_REL_DUE_TO_SUSPEND)
/sum(p.NBR_OF_UL_TBF+ p.NBR_OF_DL_TBF- p.UL_TBF_Establishment_Failed - p.DL_TBF_Establishment_Failed- p.UL_EGPRS_TBF_REL_DUE_NO_RESP - p.DL_EGPRS_TBF_REL_DUE_NO_RESP-p.UL_TBF_REL_DUE_TO_FLUSH-p.DL_TBF_REL_DUE_TO_FLUSH- p.UL_TBF_REL_DUE_TO_SUSPEND-p.DL_TBF_REL_DUE_TO_SUSPEND)
)
,2) tbf_34a /*未驗(yàn)證*/
,Sum(p.UL_TBF_Establishment_Failed + p.DL_TBF_Establishment_Failed) tbf_est_fail
,Sum( p.UL_EGPRS_TBF_REL_DUE_NO_RESP + p.DL_EGPRS_TBF_REL_DUE_NO_RESP) egprs_tbf_no_res
, sum(NBR_OF_UL_TBF + NBR_OF_DL_TBF
- decode(AVE_DUR_UL_TBF_SUM,0,0,AVE_DUR_UL_TBF_DEN)
- decode(AVE_DUR_DL_TBF_SUM,0,0,AVE_DUR_DL_TBF_DEN)
- UL_TBF_REL_DUE_TO_FLUSH - DL_TBF_REL_DUE_TO_FLUSH
- UL_TBF_REL_DUE_TO_SUSPEND - DL_TBF_REL_DUE_TO_SUSPEND) tbf_34_numric
,sum(NBR_OF_UL_TBF + NBR_OF_DL_TBF
- UL_TBF_REL_DUE_TO_FLUSH - DL_TBF_REL_DUE_TO_FLUSH
- UL_TBF_REL_DUE_TO_SUSPEND - DL_TBF_REL_DUE_TO_SUSPEND)tbf_34_denom
,round(decode(sum(NBR_OF_UL_TBF + NBR_OF_DL_TBF
- UL_TBF_REL_DUE_TO_FLUSH - DL_TBF_REL_DUE_TO_FLUSH
- UL_TBF_REL_DUE_TO_SUSPEND - DL_TBF_REL_DUE_TO_SUSPEND)
,0,-1,
100-100*sum(NBR_OF_UL_TBF + NBR_OF_DL_TBF
- decode(AVE_DUR_UL_TBF_SUM,0,0,AVE_DUR_UL_TBF_DEN)
- decode(AVE_DUR_DL_TBF_SUM,0,0,AVE_DUR_DL_TBF_DEN)
- UL_TBF_REL_DUE_TO_FLUSH - DL_TBF_REL_DUE_TO_FLUSH
- UL_TBF_REL_DUE_TO_SUSPEND - DL_TBF_REL_DUE_TO_SUSPEND)
/sum(NBR_OF_UL_TBF + NBR_OF_DL_TBF
- UL_TBF_REL_DUE_TO_FLUSH - DL_TBF_REL_DUE_TO_FLUSH
- UL_TBF_REL_DUE_TO_SUSPEND - DL_TBF_REL_DUE_TO_SUSPEND)
)
,2) tbf_34 /*TBF_SUCCESS*/
,concat(round(100*decode(sum(NBR_OF_UL_TBF),0,0,sum(NBR_OF_UL_TBF-UL_TBF_ESTABLISHMENT_FAILED-UL_EGPRS_TBF_REL_DUE_NO_RESP)/sum(NBR_OF_UL_TBF)),2),'%')
UL_TBF_SUCCESS_RATE
,concat(round(100*decode(sum(NBR_OF_DL_TBF),0,0,sum(NBR_OF_DL_TBF-DL_TBF_ESTABLISHMENT_FAILED-DL_EGPRS_TBF_REL_DUE_NO_RESP)/sum(NBR_OF_DL_TBF)),2),'%')
DL_TBF_SUCCESS_RATE
,round(decode(
Sum(p.rlc_data_blocks_dl_cs2+nvl(p.retra_rlc_data_blocks_dl_cs2,0)),0,0,
100*(Sum(nvl(p.retra_rlc_data_blocks_dl_cs2,0))/Sum(p.rlc_data_blocks_dl_cs2+nvl(p.retra_rlc_data_blocks_dl_cs2,0)))
)
,2) DL_CS2Bler
,round(decode(
sum(p.rlc_data_blocks_ul_cs1+p.rlc_data_blocks_ul_cs2+p.rlc_mac_cntrl_blocks_ul+p.bad_frame_ind_ul_cs1+p.bad_frame_ind_ul_cs2+p.bad_frame_ind_ul_unack+p.ignor_rlc_data_bl_ul_due_bsn),0,0,
(sum(p.rlc_data_blocks_ul_cs1*20+p.rlc_data_blocks_ul_cs2*30)*8/1000)/(sum(p.rlc_data_blocks_ul_cs1+p.rlc_data_blocks_ul_cs2+p.rlc_mac_cntrl_blocks_ul+p.bad_frame_ind_ul_cs1+p.bad_frame_ind_ul_cs2+p.bad_frame_ind_ul_unack+p.ignor_rlc_data_bl_ul_due_bsn)/50)
)
,2) ef_ul_tp_1tsl
,round(decode(
sum(p.rlc_data_blocks_dl_cs1+p.rlc_data_blocks_dl_cs2+p.rlc_mac_cntrl_blocks_dl+p.retra_rlc_data_blocks_dl_cs1+p.retra_rlc_data_blocks_dl_cs2),0,0,
(sum(p.rlc_data_blocks_dl_cs1*20+p.rlc_data_blocks_dl_cs2*30)*8/1000)/(sum(p.rlc_data_blocks_dl_cs1+p.rlc_data_blocks_dl_cs2+p.rlc_mac_cntrl_blocks_dl+p.retra_rlc_data_blocks_dl_cs1+p.retra_rlc_data_blocks_dl_cs2)/50)
)
,2) ef_dl_tp_1tsl
,round(decode(
Sum(p.period_duration),0,0,
(Sum(p.RLC_data_blocks_DL_CS1+p.RLC_data_blocks_DL_CS2+p.RLC_mac_cntrl_blocks_DL+p.RETRA_RLC_DATA_BLOCKS_DL_CS1+p.RETRA_RLC_DATA_BLOCKS_DL_CS2)/50)/Sum(p.period_duration*60)
)
,2) dl_ps_traf/* 此公式不是很準(zhǔn)*/
,sum(p.rlc_data_blocks_ul_cs1*20+p.rlc_data_blocks_ul_unack*20+p.rlc_data_blocks_ul_cs2*30)/1024 ul_gprs_payload
,sum(p.rlc_data_blocks_dl_cs1*20+p.rlc_data_blocks_dl_unack*20+p.rlc_data_blocks_dl_cs2*30)/1024 dl_gprs_payload

,Sum (NBR_OF_UL_TBF - EGPRS_TBFS_UL) UL_G_TBF_Est
,Sum (NBR_OF_DL_TBF - EGPRS_TBFS_DL) DL_G_TBF_Est
,Sum(P.EGPRS_TBFS_UL) UL_E_TBF
,Sum(P.EGPRS_TBFS_DL) DL_E_TBF
,Sum(P.UL_GPRS_TBF_IN_EGPRS_TERR) UL_GTBF_IN_E
,Sum(P.DL_GPRS_TBF_IN_EGPRS_TERR) DL_GTBF_IN_E
,Sum(UL_GPRS_TBF_FOR_EGPRS_REQ) UL_GTBF_FOR_E_REQ
,Sum(DL_GPRS_TBF_FOR_EGPRS_REQ) DL_GTBF_FOR_E_REQ

from objects o_bsc,objects o_bcf,objects o_bts,c_bts bts,p_nbsc_packet_control_unit p,p_nbsc_res_avail r
where
o_bts.object_class = 4 and
o_bcf.object_class = 27 and
o_bsc.object_class = 3 and
p.INT_ID=o_bsc.INT_ID and
bts.int_id=o_bts.int_id and
o_bts.parent_int_id=o_bcf.int_id and
o_bcf.parent_int_id=o_bsc.int_id and
--o_bsc.NAME LIKE '&city2digits'||'BSC%' and
o_bsc.NAME in ( 'ZGBSC638','ZGBSC637') and
--*
p.int_id=r.int_id And


p.bts_int_id=r.bts_int_id And

p.bts_int_id=bts.int_id and
bts.conf_name='<ACTUAL>' and
--bts.cell_id In( &CI) and

p.period_start_time = r.period_start_time and

p.period_start_time >= to_date(&stime_yyyymmddhh24,'yyyymmddhh24')and
p.period_start_time <= to_date(&etime_yyyymmddhh24,'yyyymmddhh24')
group by
p.int_id
,p.bts_int_id
,to_char(p.period_start_time,'yyyymmddhh24')
,o_bsc.name
,bts.la_id_lac
,bts.nsei
,bts.cell_id
,bts.segment_id
,o_bcf.object_instance
,o_bts.object_instance
,o_bts.Name
,bts.EGPRS_ENABLED
,bts.GPRS_ENABLED
) gprs,
(
Select
sc.int_id int_id
,sc.bts_int_id bts_int_id
,to_char(sc.period_start_time,'yyyymmddhh24') Sdatetime
,sum(decode(cs, 1, uxx*22, 2, uxx*28, 3, uxx*37, 4, uxx*44, 5, uxx*56, 6, uxx*74, 7, (uxx/2)*112, 8, (uxx/2)*136, 9, (uxx/2)*148,0))/1024 ulegpr_payload
,sum(decode(cs, 1, dxx*22, 2, dxx*28, 3, dxx*37, 4, dxx*44, 5, dxx*56, 6, dxx*74, 7, (dxx/2)*112, 8, (dxx/2)*136, 9, (dxx/2)*148,0))/1024 dlegpr_payload
,sum(decode(cs, 1, dxx*22, 0))*8/1024/3600dl_mcs1_payload
,sum(decode(cs, 2, dxx*28, 0))*8/1024/3600dl_mcs2_payload
,sum(decode(cs, 3, dxx*37, 0))*8/1024/3600dl_mcs3_payload
,sum(decode(cs, 4, dxx*44, 0))*8/1024/3600dl_mcs4_payload
,sum(decode(cs, 5, dxx*56, 0))*8/1024/3600dl_mcs5_payload
,sum(decode(cs, 6, dxx*74, 0))*8/1024/3600dl_mcs6_payload
,sum(decode(cs, 7, (dxx/2)*112, 0))*8/1024/3600dl_mcs7_payload
,sum(decode(cs, 8, (dxx/2)*136, 0))*8/1024/3600dl_mcs8_payload
,sum(decode(cs, 9, (dxx/2)*148, 0))*8/1024/3600dl_mcs9_payload
,decode( sum(decode(cs, 1, dxx*22, 2, dxx*28, 3, dxx*37, 4, dxx*44, 5, dxx*56, 6, dxx*74, 7, (dxx/2)*112, 8, (dxx/2)*136, 9, (dxx/2)*148,0)),0,0,
sum(decode(cs,7, (dxx/2)*112, 8, (dxx/2)*136, 9, (dxx/2)*148,0))/
sum(decode(cs, 1, dxx*22, 2, dxx*28, 3, dxx*37, 4, dxx*44, 5, dxx*56, 6, dxx*74, 7, (dxx/2)*112, 8, (dxx/2)*136, 9, (dxx/2)*148,0))) dl_high_code_rate
,decode(
sum(decode(cs, 1, uzz, 2, uzz, 3, uzz, 4, uzz, 5, uzz, 6, uzz, 7, (uzz/2), 8, (uzz/2), 9, (uzz/2),0))/50,0,-1,
sum(decode(cs, 1, uyy*22, 2, uyy*28, 3, uyy*37, 4, uyy*44, 5, uyy*56, 6, uyy*74, 7, (uyy/2)*112, 8, (uyy/2)*136, 9, (uyy/2)*148,0))*8/1000
/(sum(decode(cs, 1, uzz, 2, uzz, 3, uzz, 4, uzz, 5, uzz, 6, uzz, 7, (uzz/2), 8, (uzz/2), 9, (uzz/2),0))/50)
) ef_ack_ul_tp_1tsl_trf_234
,decode(
sum(decode(cs, 1, dzz, 2, dzz, 3, dzz, 4, dzz, 5, dzz, 6, dzz, 7, (dzz/2), 8, (dzz/2), 9, (dzz/2),0))/50,0,-1,
sum(decode(cs, 1, dyy*22, 2, dyy*28, 3, dyy*37, 4, dyy*44, 5, dyy*56, 6, dyy*74, 7, (dyy/2)*112, 8, (dyy/2)*136, 9, (dyy/2)*148,0))*8/1000
/(sum(decode(cs, 1, dzz, 2, dzz, 3, dzz, 4, dzz, 5, dzz, 6, dzz, 7, (dzz/2), 8, (dzz/2), 9, (dzz/2),0))/50)
) ef_ack_dl_tp_1tsl_trf_236
From
(
select
code.int_id
,code.bts_int_id
,code.period_start_time
,code.coding_scheme cs
,(UL_RLC_BLOCKS_IN_ACK_MODE + UL_RLC_BLOCKS_IN_UNACK_MODE) uxx
,(DL_RLC_BLOCKS_IN_ACK_MODE + DL_RLC_BLOCKS_IN_UNACK_MODE) dxx
,(ul_rlc_blocks_in_ack_mode) uyy
,(ul_rlc_blocks_in_ack_mode + retrans_rlc_data_blocks_ul) uzz
,(dl_rlc_blocks_in_ack_mode) dyy
,(dl_rlc_blocks_in_ack_mode + retrans_rlc_data_blocks_dl) dzz
from objects o_bsc,objects o_bcf,objects o_bts,c_bts bts,p_nbsc_coding_scheme code
where
o_bts.object_class = 4 and
o_bcf.object_class = 27 and
o_bsc.object_class = 3 and
code.INT_ID=o_bsc.INT_ID and
bts.int_id=o_bts.int_id and
o_bts.parent_int_id=o_bcf.int_id and
o_bcf.parent_int_id=o_bsc.int_id and
--o_bsc.NAME LIKE '&city2digits'||'BSC%' and
o_bsc.NAME in ( 'CJNBSC11','CJNBSC12','CJNBSC13','CJNBSC14','CJNBSC15','CJNBSC16') and
--*
code.bts_int_id=bts.int_id and
bts.conf_name='<ACTUAL>' and
--bts.cell_id In(&CI) and
code.period_start_time >= to_date(&stime_yyyymmddhh24,'yyyymmddhh24') and
code.period_start_time <= to_date(&etime_yyyymmddhh24,'yyyymmddhh24') and
code.coding_scheme in (1,2,3,4,5,6,7,8,9)
) sc
Group Bysc.int_id
,sc.bts_int_id
,to_char(sc.period_start_time,'yyyymmddhh24')
) edge,
(
Select
t.int_id int_id
,t.bts_int_id bts_int_id
,to_char(t.period_start_time,'yyyymmddhh24') Sdatetime
,sum(t.gprs_ter_ug_rej_due_csw_tr) cs_rej
,sum(t.gprs_ter_ug_rej_due_lack_psw) ps_rej
,sum(t.gprs_ter_ug_rej_due_lack_pcu) pcu_rej
,sum(t.gprs_ter_upgrd_req) up_req
from objects o_bsc,objects o_bcf,objects o_bts,c_bts bts,p_nbsc_traffic t
where
o_bts.object_class = 4 and
o_bcf.object_class = 27 and
o_bsc.object_class = 3 and
t.INT_ID=o_bsc.INT_ID and
bts.int_id=o_bts.int_id and
o_bts.parent_int_id=o_bcf.int_id and
o_bcf.parent_int_id=o_bsc.int_id and
--o_bsc.NAME LIKE '&city2digits'||'BSC%' and
o_bsc.NAME in ( 'ZGBSC638','ZGBSC637') and
--*
t.bts_int_id=bts.int_id and
bts.conf_name='<ACTUAL>' and
--bts.cell_id In( &CI) and
t.period_start_time <= to_date(&etime_yyyymmddhh24,'yyyymmddhh24') and
t.period_start_time >= to_date(&stime_yyyymmddhh24,'yyyymmddhh24')
Group By t.int_id
,t.bts_int_id
,to_char(t.period_start_time,'yyyymmddhh24')
)traffic,
(
Select
r_a.int_id int_id
,r_a.bts_int_id bts_int_id
,to_char(r_a.period_start_time,'yyyymmddhh24') Sdatetime
,sum(r_a.imm_assgn_sent) imm_assgn_sent
,sum(r_a.imm_assgn_rej) imm_assgn_rej
from objects o_bsc,objects o_bcf,objects o_bts,c_bts bts,P_NBSC_RES_ACCESS r_a
where
o_bts.object_class = 4 and
o_bcf.object_class = 27 and
o_bsc.object_class = 3 and
r_a.INT_ID=o_bsc.INT_ID and
bts.int_id=o_bts.int_id and
o_bts.parent_int_id=o_bcf.int_id and
o_bcf.parent_int_id=o_bsc.int_id and
--o_bsc.NAME LIKE '&city2digits'||'BSC%' and
o_bsc.NAME in( 'ZGBSC638','ZGBSC637')and
--*
r_a.bts_int_id=bts.int_id and
bts.conf_name='<ACTUAL>' and
--bts.cell_id In( &CI) and
r_a.period_start_time <= to_date(&etime_yyyymmddhh24,'yyyymmddhh24') and
r_a.period_start_time >= to_date(&stime_yyyymmddhh24,'yyyymmddhh24')
Group By r_a.int_id
,r_a.bts_int_id
,to_char(r_a.period_start_time,'yyyymmddhh24')

)acc

Where gprs.int_id = edge.int_id (+) And
gprs.int_id = traffic.int_id (+) And
gprs.int_id = acc.int_id (+) And
gprs.bts_int_id = edge.bts_int_id (+) And
gprs.bts_int_id = traffic.bts_int_id (+) And
gprs.bts_int_id = acc.bts_int_id (+) And
gprs.Sdatetime = edge.Sdatetime (+) And
gprs.Sdatetime = traffic.Sdatetime (+) And
gprs.Sdatetime = acc.Sdatetime (+)