OraFAQ Forum: SQL & PL/SQL » creation of view by passing the values dynamically

Home » SQL & PL/SQL » SQL & PL/SQL » creation of view by passing the values dynamically (11.2.0.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

creation of view by passing the values dynamically [message #630965]

Wed, 07 January 2015 00:41

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Hi,

I have a query and the query is accepting the p_k column value. I would like to create the view by passing p_k column value dynamically. I don't want to give the query to users directly so I would like to create the view. Please suggest on this or any alternate solution exist for my problem.


with inu as
   (select icnu.ext_bill_nr, count(1) cnt, 
	sum((decode(icnu.transaction_type_88,'1',1,0) + (decode(icnu.transaction_type_88,'2',1,0)) + (decode(icnu.transaction_type_88,'3',1,0))) * 
	    ((decode(d.document_type, 'IN', 1,0) * decode(icnu.discount_item_flag,'N',1,0) * decode(icnu.bill_section_flag_88, '9', 0,1)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icnu.discount_item_flag,'N',1,0) * decode(substr(icnu.bill_section_sort,'9',1),'9',0,1)))) c_cnt_rs, 
	sum((decode(icnu.transaction_type_88,'1',1,0) + (decode(icnu.transaction_type_88,'2',1,0)) + (decode(icnu.transaction_type_88,'3',1,0))) *
	    ((decode(d.document_type, 'IN', 1,0) * decode(icnu.discount_item_flag,'N',1,0) * decode(icnu.bill_section_flag_88, '9', 1,0)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icnu.discount_item_flag,'N',1,0) * decode(substr(icnu.bill_section_sort,'9',1),'9',1,0)))) c_cnt_rc,
	sum((decode(icnu.transaction_type_88,'4',1,0) + (decode(icnu.transaction_type_88,'5',1,0))) * 
	    ((decode(d.document_type, 'IN', 1,0) * decode(icnu.discount_item_flag,'N',1,0) * decode(icnu.bill_section_flag_88, '9', 0,1)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icnu.discount_item_flag,'N',1,0) * decode(substr(icnu.bill_section_sort,'9',1),'9',0,1)))) c_cnt_ns, 
	sum((decode(icnu.transaction_type_88,'4',1,0) + (decode(icnu.transaction_type_88,'5',1,0))) * 
	    ((decode(d.document_type, 'IN', 1,0) * decode(icnu.discount_item_flag,'N',1,0) * decode(icnu.bill_section_flag_88, '9', 1,0)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icnu.discount_item_flag,'N',1,0) * decode(substr(icnu.bill_section_sort,'9',1),'9',1,0)))) c_cnt_nc,
	sum((decode(icnu.transaction_type_88,'1',1,0) + (decode(icnu.transaction_type_88,'2',1,0)) + (decode(icnu.transaction_type_88,'3',1,0))) * 
	    ((decode(d.document_type, 'IN', 1,0) * decode(icnu.discount_item_flag,'N',0,1) * decode(icnu.bill_section_flag_88, '9', 0,1)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icnu.discount_item_flag,'N',0,1) * decode(substr(icnu.bill_section_sort,'9',1),'9',0,1)))) d_cnt_rs, 
	sum((decode(icnu.transaction_type_88,'1',1,0) + (decode(icnu.transaction_type_88,'2',1,0)) + (decode(icnu.transaction_type_88,'3',1,0))) * 
	    ((decode(d.document_type, 'IN', 1,0) * decode(icnu.discount_item_flag,'N',0,1) * decode(icnu.bill_section_flag_88, '9', 1,0)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icnu.discount_item_flag,'N',0,1) * decode(substr(icnu.bill_section_sort,'9',1),'9',1,0)))) d_cnt_rc,
	sum((decode(icnu.transaction_type_88,'4',1,0) + (decode(icnu.transaction_type_88,'5',1,0))) * 
	    ((decode(d.document_type, 'IN', 1,0) * decode(icnu.discount_item_flag,'N',0,1) * decode(icnu.bill_section_flag_88, '9', 0,1)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icnu.discount_item_flag,'N',0,1) * decode(substr(icnu.bill_section_sort,'9',1),'9',0,1)))) d_cnt_ns, 
	sum((decode(icnu.transaction_type_88,'4',1,0) + (decode(icnu.transaction_type_88,'5',1,0))) * 
	    ((decode(d.document_type, 'IN', 1,0) * decode(icnu.discount_item_flag,'N',0,1) * decode(icnu.bill_section_flag_88, '9', 1,0)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icnu.discount_item_flag,'N',0,1) * decode(substr(icnu.bill_section_sort,'9',1),'9',1,0)))) d_cnt_nc,
	sum(decode(icnu.transaction_type_88,'6',1,0)) d_cnt_info,
	sum((icnu.item_amount_excl_vat) * (decode(icnu.transaction_type_88,'1',1,0) + (decode(icnu.transaction_type_88,'2',1,0)) + (decode(icnu.transaction_type_88,'3',1,0))) *
	    ((decode(icnu.bill_section_flag_88, '9', 0,1) * decode(d.document_type, 'IN', 1,0)) +
	    (decode(substr(icnu.bill_section_sort,'9',1), '9', 0,1) * decode(d.document_type, 'CN',1,0)))) inuat_rs,
	sum((icnu.item_amount_excl_vat) * (decode(icnu.transaction_type_88,'1',1,0) + (decode(icnu.transaction_type_88,'2',1,0)) + (decode(icnu.transaction_type_88,'3',1,0))) *
	    ((decode(icnu.bill_section_flag_88, '9', 1,0) * decode(d.document_type, 'IN', 1,0)) +
	    (decode(substr(icnu.bill_section_sort,'9',1), '9', 1,0) * decode(d.document_type, 'CN',1,0)))) inuat_rc,
	sum((icnu.item_amount_excl_vat) * (decode(icnu.transaction_type_88,'4',1,0) + (decode(icnu.transaction_type_88,'5',1,0))) *
	    ((decode(icnu.bill_section_flag_88, '9', 0,1) * decode(d.document_type, 'IN', 1,0)) +
	    (decode(substr(icnu.bill_section_sort,'9',1), '9', 0,1) * decode(d.document_type, 'CN',1,0)))) inuat_ns,
	sum((icnu.item_amount_excl_vat) * (decode(icnu.transaction_type_88,'4',1,0) + (decode(icnu.transaction_type_88,'5',1,0))) *
	    ((decode(icnu.bill_section_flag_88, '9', 1,0) * decode(d.document_type, 'IN', 1,0)) +
	    (decode(substr(icnu.bill_section_sort,'9',1), '9', 1,0) * decode(d.document_type, 'CN',1,0)))) inuat_nc,
	sum((icnu.item_amount_excl_vat) * (decode(icnu.transaction_type_88,'6',1,0))) inuat_d_info,
	sum(icnu.item_amount_excl_vat) inuat
	from init_chrg_it_nu icnu, docu d
	where icnu.p_k = 201412270100
	and icnu.p_k = d.p_k
	and icnu.ext_bill_nr = d.document_number (+)
	and d.bill_format in ('MMII','MMIE')
	group by icnu.ext_bill_nr),
    iu as
	(select icu.ext_bill_nr, count(1) cnt, 
	sum((decode(d.document_type, 'IN', 1,0) * decode(icu.discount_item_flag,'N',1,0) * decode(icu.bill_section_flag_88, '9', 0,1)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icu.discount_item_flag,'N',1,0) * decode(substr(icu.bill_section_sort,'9',1),'9',0,1))) c_cnt_s, 
	sum((decode(d.document_type, 'IN', 1,0) * decode(icu.discount_item_flag,'N',1,0) * decode(icu.bill_section_flag_88, '9', 1,0)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icu.discount_item_flag,'N',1,0) * decode(substr(icu.bill_section_sort,'9',1),'9',1,0))) c_cnt_c,
	sum((decode(d.document_type, 'IN', 1,0) * decode(icu.discount_item_flag,'N',0,1) * decode(icu.bill_section_flag_88, '9', 0,1)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icu.discount_item_flag,'N',0,1) * decode(substr(icu.bill_section_sort,'9',1),'9',0,1))) d_cnt_s, 
	sum((decode(d.document_type, 'IN', 1,0) * decode(icu.discount_item_flag,'N',0,1) * decode(icu.bill_section_flag_88, '9', 1,0)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icu.discount_item_flag,'N',0,1) * decode(substr(icu.bill_section_sort,'9',1),'9',1,0))) d_cnt_c,
	sum(decode(icu.transaction_type_88,'6',1,0)) d_cnt_info,
	sum((icu.item_amount_excl_vat) *
	    ((decode(icu.bill_section_flag_88, '9', 0,1) * decode(d.document_type, 'IN', 1,0)) +
	    (decode(substr(icu.bill_section_sort,'9',1), '9', 0,1) * decode(d.document_type, 'CN',1,0)))) iuat_s,
	sum((icu.item_amount_excl_vat) *
	    ((decode(icu.bill_section_flag_88, '9', 1,0) * decode(d.document_type, 'IN', 1,0)) +
	    (decode(substr(icu.bill_section_sort,'9',1), '9', 1,0) * decode(d.document_type, 'CN',1,0)))) iuat_c,
	sum((icu.item_amount_excl_vat) * (decode(icu.transaction_type_88,'6',1,0))) iuat_d_info,
	sum(icu.item_amount_excl_vat) iuat
	from init_chrg_it_u icu, docu d
	where icu.p_k = 201412270100
	and icu.p_k = d.p_k
	and icu.ext_bill_nr = d.document_number (+)
	and d.bill_format in ('MMII','MMIE')
	group by icu.ext_bill_nr),
    u as
	(select document_number, count(1) cnt, 
	sum(decode(substr(usg_type,1,7),'CONTENT',1,0)) cnt_c,
	sum(decode(substr(usg_type,1,7),'CONTENT',0,1)) cnt_s,
	sum(decode(substr(usg_type,1,7),'CONTENT',bill_amount,0)) at_c,
	sum(decode(substr(usg_type,1,7),'CONTENT',0,bill_amount)) at_s,
	sum((events_total) * (decode(substr(usg_type,1,7),'CONTENT',1,0))) et_cnt_c,
	sum((events_total) * (decode(substr(usg_type,1,7),'CONTENT',0,1))) et_cnt_s,
	sum(decode(substr(usg_type,1,7),'CONTENT',gross_amount,0)) gt_c,
	sum(decode(substr(usg_type,1,7),'CONTENT',0,gross_amount)) gt_s
	from usg
	where p_k = 201412270100
	group by document_number), 
    ud as
	(select document_number, 
	sum(decode(substr(usg_type,1,7),'CONTENT',1,0)) cnt_c,
	sum(decode(substr(usg_type,1,7),'CONTENT',0,1)) cnt_s,
	sum(decode(substr(usg_type,1,7),'CONTENT',gross_amount,0)) gt_c,
	sum(decode(substr(usg_type,1,7),'CONTENT',0,gross_amount)) gt_s
	from usg_det
	where p_k = 201412270100
	group by document_number),
    r as
	(select document_number, count(1) cnt, 
	sum(decode(substr(rc_type,1,7),'CONTENT',0,1)) cnt_s,
	sum(decode(substr(rc_type,1,7),'CONTENT',1,0)) cnt_c,
	sum(decode(substr(rc_type,1,7),'CONTENT',0,bill_amount)) at_s,
	sum(decode(substr(rc_type,1,7),'CONTENT',bill_amount,0)) at_c 
	from rc
	where p_k = 201412270100
	group by document_number),
    n as
	(select document_number, count(1) cnt, 
	sum(decode(substr(nrc_type,1,7),'CONTENT',0,1)) cnt_s,
	sum(decode(substr(nrc_type,1,7),'CONTENT',1,0)) cnt_c,
	sum(decode(substr(nrc_type,1,7),'CONTENT',0,bill_amount)) at_s,
	sum(decode(substr(nrc_type,1,7),'CONTENT',bill_amount,0)) at_c 
	from nrc
	where p_k = 201412270100
	group by document_number),
    ds as
	(select document_number, count(1) cnt, 
	sum((decode(substr(disc_type,1,12),'DISC-CONTENT',0,1)) * (decode(substr(disc_type,-3,3),'-RC',1,0))) cnt_rs,
	sum((decode(substr(disc_type,1,12),'DISC-CONTENT',0,1)) * (decode(substr(disc_type,-3,3),'NRC',1,0))) cnt_ns,
	sum((decode(substr(disc_type,1,12),'DISC-CONTENT',0,1)) * (decode(substr(disc_type,-3,3),'USG',1,0))) cnt_us,
	sum((decode(substr(disc_type,1,12),'DISC-CONTENT',1,0)) * (decode(substr(disc_type,-3,3),'-RC',1,0))) cnt_rc,
	sum((decode(substr(disc_type,1,12),'DISC-CONTENT',1,0)) * (decode(substr(disc_type,-3,3),'NRC',1,0))) cnt_nc,
	sum((decode(substr(disc_type,1,12),'DISC-CONTENT',1,0)) * (decode(substr(disc_type,-3,3),'USG',1,0))) cnt_uc,
	sum(decode(substr(disc_type,1,9),'DISC-INFO',1,0)) cnt_info,
	sum(bill_amount * (decode(substr(disc_type,1,12),'DISC-CONTENT',0,1)) * (decode(substr(disc_type,-3,3),'-RC',1,0))) at_rs,
	sum(bill_amount * (decode(substr(disc_type,1,12),'DISC-CONTENT',0,1)) * (decode(substr(disc_type,-3,3),'NRC',1,0))) at_ns,
	sum(bill_amount * (decode(substr(disc_type,1,12),'DISC-CONTENT',0,1)) * (decode(substr(disc_type,-3,3),'USG',1,0))) at_us,
	sum(bill_amount * (decode(substr(disc_type,1,12),'DISC-CONTENT',1,0)) * (decode(substr(disc_type,-3,3),'-RC',1,0))) at_rc,
	sum(bill_amount * (decode(substr(disc_type,1,12),'DISC-CONTENT',1,0)) * (decode(substr(disc_type,-3,3),'NRC',1,0))) at_nc,
	sum(bill_amount * (decode(substr(disc_type,1,12),'DISC-CONTENT',1,0)) * (decode(substr(disc_type,-3,3),'USG',1,0))) at_uc,
	sum(bill_amount * (decode(substr(disc_type,1,9),'DISC-INFO',1,0))) at_info
	from disc
	where p_k = 201412270100
	group by document_number)
select
	dtr.document_number, dtr.document_status DOC_STATUS,
	decode(NVL(inu.c_cnt_rs,0) - NVL(r.cnt_s,0), 0,'N','Y') STD_CHG_RC_CNT_ERR,
	decode(NVL(inu.c_cnt_ns,0) - NVL(n.cnt_s,0), 0,'N','Y') STD_CHG_NRC_CNT_ERR,
	decode(NVL(inu.d_cnt_rs,0) - NVL(ds.cnt_rs,0), 0,'N','Y') STD_DIS_RC_CNT_ERR,
	decode(NVL(inu.d_cnt_ns,0) - NVL(ds.cnt_ns,0), 0,'N','Y') STD_DIS_NRC_CNT_ERR,
	decode(SIGN(NVL(r.cnt_c,0) - NVL(inu.c_cnt_rc,0)), -1,'Y','N') CON_CHG_RC_CNT_LESS,
	decode(SIGN(NVL(n.cnt_c,0) - NVL(inu.c_cnt_nc,0)), -1,'Y','N') CON_CHG_NRC_CNT_LESS,
	decode(SIGN(NVL(ds.cnt_rc,0) - NVL(inu.d_cnt_rc,0)), -1,'Y','N') STD_DIS_RC_CNT_LESS,
	decode(SIGN(NVL(ds.cnt_nc,0) - NVL(inu.d_cnt_nc,0)), -1,'Y','N') STD_DIS_NRC_CNT_LESS,
	decode(NVL(inu.inuat_rs,0) - (NVL(r.at_s,0) + NVL(ds.at_rs,0)), 0,'N','Y') STD_RC_AMT_ERR,
	decode(NVL(inu.inuat_rc,0) - (NVL(r.at_c,0) + NVL(ds.at_rc,0)), 0,'N','Y') CON_RC_AMT_ERR,
	decode(NVL(inu.inuat_ns,0) - (NVL(n.at_s,0) + NVL(ds.at_ns,0)), 0,'N','Y') STD_NRC_AMT_ERR,
	decode(NVL(inu.inuat_nc,0) - (NVL(n.at_c,0) + NVL(ds.at_nc,0)), 0,'N','Y') CON_NRC_AMT_ERR,
	decode(NVL(iu.c_cnt_s,0) - NVL(u.cnt_s,0), 0,'N','Y') STD_CHG_USG_CNT_ERR,
	decode(SIGN(NVL(u.cnt_c,0) - NVL(iu.c_cnt_c,0)), -1,'Y','N') CON_CHG_USG_CNT_LESS,
	decode(NVL(iu.d_cnt_s,0) - NVL(ds.cnt_us,0), 0,'N','Y') STD_DIS_USG_CNT_ERR,
	decode(SIGN(NVL(ds.cnt_uc,0) - NVL(iu.d_cnt_c,0)), -1,'Y','N') CON_DISC_USG_CNT_LESS,
	decode(NVL(iu.iuat_s,0) - (NVL(u.at_s,0) + NVL(ds.at_us,0)), 0,'N','Y') STD_USG_AMT_ERR,
	decode(NVL(iu.iuat_c,0) - (NVL(u.at_c,0) + NVL(ds.at_uc,0)), 0,'N','Y') CON_USG_AMT_ERR,
	decode((NVL(inu.d_cnt_info,0) + NVL(iu.d_cnt_info,0))- NVL(ds.cnt_info,0), 0,'N','Y') INF_DIS_CNT_ERR,
	decode((NVL(inu.inuat_d_info,0) + NVL(iu.iuat_d_info,0))- NVL(ds.at_info, 0), 0,'N','Y') INF_DIS_AMT_ERR,
	decode(NVL(u.et_cnt_s,0) - NVL(ud.cnt_s,0), 0,'N','Y') U_UD_STD_CNT_ERR,
	decode(NVL(u.et_cnt_c,0) - NVL(ud.cnt_c,0), 0,'N','Y') U_UD_CON_CNT_ERR,
	decode(NVL(u.gt_s,0) - NVL(ud.gt_s,0), 0,'N','Y') U_UD_STD_AMT_ERR,
	decode(NVL(u.gt_c,0) - NVL(ud.gt_c,0), 0,'N','Y') U_UD_CON_AMT_ERR,
	NVL(inu.c_cnt_rs, 0) INU_STD_CHG_RC_CNT, NVL(r.cnt_s, 0) STD_RC_CNT,
	NVL(inu.c_cnt_ns, 0) INU_STD_CHG_NRC_CNT, NVL(n.cnt_s, 0) STD_NRC_CNT,
	NVL(inu.d_cnt_rs, 0) INU_STD_DIS_RC_CNT, NVL(ds.cnt_rs, 0) STD_DIS_RC_CNT,
	NVL(inu.d_cnt_ns, 0) INU_STD_DIS_NRC_CNT, NVL(ds.cnt_ns, 0) STD_DIS_NRC_CNT,
	NVL(inu.c_cnt_rc, 0) INU_CON_CHG_RC_CNT, NVL(r.cnt_c, 0) CON_RC_CNT,
	NVL(inu.c_cnt_nc, 0) INU_CON_CHG_NRC_CNT, NVL(n.cnt_c, 0) CON_NRC_CNT,
	NVL(inu.d_cnt_rc, 0) INU_CON_DIS_RC_CNT, NVL(ds.cnt_rc, 0) CON_DIS_RC_CNT,
	NVL(inu.d_cnt_nc, 0) INU_CON_DIS_NRC_CNT, NVL(ds.cnt_nc, 0) CON_DIS_NRC_CNT,
	NVL(inu.inuat_rs, 0) INU_STD_RC_TOT_AMT, NVL(r.at_s,0) STD_RC_AMT, NVL(ds.at_rs,0) STD_RC_DIS_AMT,
	NVL(inu.inuat_rc, 0) INU_CON_RC_TOT_AMT, NVL(r.at_c,0) CON_RC_AMT, NVL(ds.at_rc,0) CON_RC_DIS_AMT,
	NVL(inu.inuat_ns, 0) INU_STD_NRC_TOT_AMT, NVL(n.at_s,0) STD_NRC_AMT,  NVL(ds.at_ns,0) STD_NRC_DIS_AMT,
	NVL(inu.inuat_nc, 0) INU_CON_NRC_TOT_AMT, NVL(n.at_c,0) CON_NRC_AMT, NVL(ds.at_nc,0) CON_NRC_DIS_AMT,
	NVL(iu.c_cnt_s, 0) IU_STD_CHG_CNT, NVL(u.cnt_s, 0) STD_USG_CNT,
	NVL(iu.c_cnt_c, 0) IU_CON_CHG_CNT, NVL(u.cnt_c, 0) CON_USG_CNT, 
	NVL(iu.d_cnt_s,0) IU_STD_DIS_CNT, NVL(ds.cnt_us,0) STD_DIS_USG_CNT,
	NVL(iu.d_cnt_c,0) IU_CON_DIS_CNT, NVL(ds.cnt_uc,0) CON_DIS_USG_CNT,
	NVL(iu.iuat_s, 0) IU_STD_USG_AMT, NVL(u.at_s,0) STD_USG_AMT, NVL(ds.at_us,0) STD_USG_DIS_AMT,
	NVL(iu.iuat_c, 0) IU_CON_USG_AMT, NVL(u.at_c,0) CON_USG_AMT, NVL(ds.at_uc,0) CON_USG_DIS_AMT,
	NVL(inu.d_cnt_info,0) INU_INF_DIS_CNT, NVL(iu.d_cnt_info,0) IU_INF_DIS_CNT, NVL(ds.cnt_info,0) INF_DIS_CNT,
	NVL(inu.inuat_d_info,0) INU_INF_DIS_AMT, NVL(iu.iuat_d_info,0) IU_INF_DIS_AMT, NVL(ds.at_info,0) INF_DIS_AMT,
	NVL(u.et_cnt_s,0) STD_USG_EV_TOT, NVL(ud.cnt_s,0) STD_USGD_CNT,
	NVL(u.et_cnt_c,0) CON_USG_EV_TOT, NVL(ud.cnt_c,0) CON_USGD_CNT,
	NVL(u.gt_s,0) STD_USG_GAMT, NVL(ud.gt_s,0) STD_USGD_GAMT,
	NVL(u.gt_c,0) CON_USG_GAMT, NVL(ud.gt_c,0) CON_USGD_GAMT
from inu, iu, r, n, u, ud, ds, docu_track dtr
	where dtr.p_k = 201412270100
	and dtr.document_number = inu.ext_bill_nr (+)
	and dtr.document_number = iu.ext_bill_nr (+)
	and dtr.document_number = r.document_number (+)
	and dtr.document_number = n.document_number (+)
	and dtr.document_number = u.document_number (+)
	and dtr.document_number = ud.document_number (+)
	and dtr.document_number = ds.document_number (+)
	and (NVL(inu.c_cnt_rs,0) <> NVL(r.cnt_s,0)
	or NVL(inu.c_cnt_ns,0) <> NVL(n.cnt_s,0)
	or NVL(inu.c_cnt_rc,0) > NVL(r.cnt_c,0)
	or NVL(inu.c_cnt_nc,0) > NVL(n.cnt_c,0)
	or NVL(inu.d_cnt_rs,0) <> NVL(ds.cnt_rs,0)
	or NVL(inu.d_cnt_ns,0) <> NVL(ds.cnt_ns,0)
	or NVL(inu.d_cnt_rc,0) > NVL(ds.cnt_rc,0)
	or NVL(inu.d_cnt_nc,0) > NVL(ds.cnt_nc,0)
	or NVL(inu.inuat_rs,0) <> (NVL(r.at_s,0) + NVL(ds.at_rs,0))
	or NVL(inu.inuat_rc,0) <> (NVL(r.at_c,0) + NVL(ds.at_rc,0))
	or NVL(inu.inuat_ns,0) <> (NVL(n.at_s,0) + NVL(ds.at_ns,0))
	or NVL(inu.inuat_nc,0) <> (NVL(n.at_c,0) + NVL(ds.at_nc,0))
	or NVL(iu.c_cnt_s,0) <> NVL(u.cnt_s,0)
	or NVL(iu.d_cnt_s,0) <> NVL(ds.cnt_us,0)
	or NVL(iu.c_cnt_c,0) > NVL(u.cnt_c,0)
	or NVL(iu.d_cnt_c,0) > NVL(ds.cnt_uc,0)
	or NVL(iu.iuat_s,0) <> (NVL(u.at_s,0) + NVL(ds.at_us,0))
	or NVL(iu.iuat_c,0) <> (NVL(u.at_c,0) + NVL(ds.at_uc,0))
	or NVL(inu.d_cnt_info,0) + NVL(iu.d_cnt_info,0) <> NVL(ds.cnt_info,0)
	or NVL(inu.inuat_d_info,0) + NVL(iu.iuat_d_info,0) <> NVL(ds.at_info,0)
	or NVL(u.et_cnt_s,0) <> NVL(ud.cnt_s,0)
	or NVL(u.et_cnt_c,0) <> NVL(ud.cnt_c,0)
	or NVL(u.gt_s,0) <> NVL(ud.gt_s,0)
	or NVL(u.gt_c,0) <> NVL(ud.gt_c,0));

Report message to a moderator

Re: creation of view by passing the values dynamically [message #630967 is a reply to message #630965]

Wed, 07 January 2015 01:09

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

You can use context variable to set the parameter values. Here is an OraFAQ article by Kevin Meade. Probably, this AskTom link is also useful.

Regards,
Lalit

Report message to a moderator

Re: creation of view by passing the values dynamically [message #630968 is a reply to message #630965]

Wed, 07 January 2015 01:10

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Replace the constant (201412270100) by a context or a package variable that the user has to set before calling the view.

Report message to a moderator

Re: creation of view by passing the values dynamically [message #630969 is a reply to message #630967]

Wed, 07 January 2015 01:11

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Sorry, missed to post Kevin's article... here it is http://www.orafaq.com/node/1922

Report message to a moderator

Re: creation of view by passing the values dynamically [message #630985 is a reply to message #630965]

Wed, 07 January 2015 06:57

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

I prefer pipelined table function.

SY.

Report message to a moderator

Re: creation of view by passing the values dynamically [message #630987 is a reply to message #630985]

Wed, 07 January 2015 07:02

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Actually, it depends what you do with the "view".
If it is included in a complex query, pipelined function prevents the optimizer from rewriting the query and limits the optimization.
In addition, you have to hint the query to give a number of rows for the pipelined function; and a pipelined function means switches between PL/SQL and SQL engines.

Report message to a moderator

Re: creation of view by passing the values dynamically [message #631004 is a reply to message #630987]

Wed, 07 January 2015 09:58

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Thanks Lalit, nice to see someone using the material we post.

OP, how about just adding PK_ID to the view text in all there right places. Then whey you query the view you can use the value you are interested. Query Transformation and Predicate Pushdown in recent database versions will make sure the final query is optimized.

Kevin

Report message to a moderator

Re: creation of view by passing the values dynamically [message #631009 is a reply to message #630987]

Wed, 07 January 2015 19:55

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Michel Cadot wrote on Wed, 07 January 2015 08:02

Actually, it depends what you do with the "view".

Right, devil is, as usual, in detail. Reason I prefer using (when possible) pipelined funtion is simple:

SELECT * FROM TABLE(pipelined_funtion(param1,param2,...))

clearly shows what is going on. With context all we see is:

SELECT * FROM context_referencing_view

without actually knowing what value context variable was set to. Same applies to package variables.

SY.

Report message to a moderator

Re: creation of view by passing the values dynamically [message #631011 is a reply to message #631009]

Wed, 07 January 2015 22:00

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

What a beautiful set of posts between Michel and SY. This may be one of the top 100 threads on OraFAQ. Notice how the posts between Michel and SY show what IT is all about: choosing between differing goals: give one get the other. Neat!

Kevin

Report message to a moderator

Re: creation of view by passing the values dynamically [message #631013 is a reply to message #631011]

Wed, 07 January 2015 22:16

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Does not his do what you want? Notice what has been done with your P_K values. The only reason this would not work is if you were unable to change the result record layout to include the P_K in the final main query because of some restriction you cannot control.

1. P_K has been put in the select list of each sub-query
2. the related predicate has been removed from each sub-query WHERE clause
3. P_K has been grouped on to make it valid as a select list item
4. P_K has been added to the main query
5. P_K participates in the joins of the main query

The view is now generic to all P_K so you may query it with the value you want at runtime.

create or replace your_view_name_here
as
with inu as
   (select icnu.p_k, icnu.ext_bill_nr, count(1) cnt, 
	sum((decode(icnu.transaction_type_88,'1',1,0) + (decode(icnu.transaction_type_88,'2',1,0)) + (decode(icnu.transaction_type_88,'3',1,0))) * 
	    ((decode(d.document_type, 'IN', 1,0) * decode(icnu.discount_item_flag,'N',1,0) * decode(icnu.bill_section_flag_88, '9', 0,1)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icnu.discount_item_flag,'N',1,0) * decode(substr(icnu.bill_section_sort,'9',1),'9',0,1)))) c_cnt_rs, 
	sum((decode(icnu.transaction_type_88,'1',1,0) + (decode(icnu.transaction_type_88,'2',1,0)) + (decode(icnu.transaction_type_88,'3',1,0))) *
	    ((decode(d.document_type, 'IN', 1,0) * decode(icnu.discount_item_flag,'N',1,0) * decode(icnu.bill_section_flag_88, '9', 1,0)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icnu.discount_item_flag,'N',1,0) * decode(substr(icnu.bill_section_sort,'9',1),'9',1,0)))) c_cnt_rc,
	sum((decode(icnu.transaction_type_88,'4',1,0) + (decode(icnu.transaction_type_88,'5',1,0))) * 
	    ((decode(d.document_type, 'IN', 1,0) * decode(icnu.discount_item_flag,'N',1,0) * decode(icnu.bill_section_flag_88, '9', 0,1)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icnu.discount_item_flag,'N',1,0) * decode(substr(icnu.bill_section_sort,'9',1),'9',0,1)))) c_cnt_ns, 
	sum((decode(icnu.transaction_type_88,'4',1,0) + (decode(icnu.transaction_type_88,'5',1,0))) * 
	    ((decode(d.document_type, 'IN', 1,0) * decode(icnu.discount_item_flag,'N',1,0) * decode(icnu.bill_section_flag_88, '9', 1,0)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icnu.discount_item_flag,'N',1,0) * decode(substr(icnu.bill_section_sort,'9',1),'9',1,0)))) c_cnt_nc,
	sum((decode(icnu.transaction_type_88,'1',1,0) + (decode(icnu.transaction_type_88,'2',1,0)) + (decode(icnu.transaction_type_88,'3',1,0))) * 
	    ((decode(d.document_type, 'IN', 1,0) * decode(icnu.discount_item_flag,'N',0,1) * decode(icnu.bill_section_flag_88, '9', 0,1)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icnu.discount_item_flag,'N',0,1) * decode(substr(icnu.bill_section_sort,'9',1),'9',0,1)))) d_cnt_rs, 
	sum((decode(icnu.transaction_type_88,'1',1,0) + (decode(icnu.transaction_type_88,'2',1,0)) + (decode(icnu.transaction_type_88,'3',1,0))) * 
	    ((decode(d.document_type, 'IN', 1,0) * decode(icnu.discount_item_flag,'N',0,1) * decode(icnu.bill_section_flag_88, '9', 1,0)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icnu.discount_item_flag,'N',0,1) * decode(substr(icnu.bill_section_sort,'9',1),'9',1,0)))) d_cnt_rc,
	sum((decode(icnu.transaction_type_88,'4',1,0) + (decode(icnu.transaction_type_88,'5',1,0))) * 
	    ((decode(d.document_type, 'IN', 1,0) * decode(icnu.discount_item_flag,'N',0,1) * decode(icnu.bill_section_flag_88, '9', 0,1)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icnu.discount_item_flag,'N',0,1) * decode(substr(icnu.bill_section_sort,'9',1),'9',0,1)))) d_cnt_ns, 
	sum((decode(icnu.transaction_type_88,'4',1,0) + (decode(icnu.transaction_type_88,'5',1,0))) * 
	    ((decode(d.document_type, 'IN', 1,0) * decode(icnu.discount_item_flag,'N',0,1) * decode(icnu.bill_section_flag_88, '9', 1,0)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icnu.discount_item_flag,'N',0,1) * decode(substr(icnu.bill_section_sort,'9',1),'9',1,0)))) d_cnt_nc,
	sum(decode(icnu.transaction_type_88,'6',1,0)) d_cnt_info,
	sum((icnu.item_amount_excl_vat) * (decode(icnu.transaction_type_88,'1',1,0) + (decode(icnu.transaction_type_88,'2',1,0)) + (decode(icnu.transaction_type_88,'3',1,0))) *
	    ((decode(icnu.bill_section_flag_88, '9', 0,1) * decode(d.document_type, 'IN', 1,0)) +
	    (decode(substr(icnu.bill_section_sort,'9',1), '9', 0,1) * decode(d.document_type, 'CN',1,0)))) inuat_rs,
	sum((icnu.item_amount_excl_vat) * (decode(icnu.transaction_type_88,'1',1,0) + (decode(icnu.transaction_type_88,'2',1,0)) + (decode(icnu.transaction_type_88,'3',1,0))) *
	    ((decode(icnu.bill_section_flag_88, '9', 1,0) * decode(d.document_type, 'IN', 1,0)) +
	    (decode(substr(icnu.bill_section_sort,'9',1), '9', 1,0) * decode(d.document_type, 'CN',1,0)))) inuat_rc,
	sum((icnu.item_amount_excl_vat) * (decode(icnu.transaction_type_88,'4',1,0) + (decode(icnu.transaction_type_88,'5',1,0))) *
	    ((decode(icnu.bill_section_flag_88, '9', 0,1) * decode(d.document_type, 'IN', 1,0)) +
	    (decode(substr(icnu.bill_section_sort,'9',1), '9', 0,1) * decode(d.document_type, 'CN',1,0)))) inuat_ns,
	sum((icnu.item_amount_excl_vat) * (decode(icnu.transaction_type_88,'4',1,0) + (decode(icnu.transaction_type_88,'5',1,0))) *
	    ((decode(icnu.bill_section_flag_88, '9', 1,0) * decode(d.document_type, 'IN', 1,0)) +
	    (decode(substr(icnu.bill_section_sort,'9',1), '9', 1,0) * decode(d.document_type, 'CN',1,0)))) inuat_nc,
	sum((icnu.item_amount_excl_vat) * (decode(icnu.transaction_type_88,'6',1,0))) inuat_d_info,
	sum(icnu.item_amount_excl_vat) inuat
	from init_chrg_it_nu icnu, docu d
--where icnu.p_k = 201412270100
	where icnu.p_k = d.p_k
	and icnu.ext_bill_nr = d.document_number (+)
	and d.bill_format in ('MMII','MMIE')
	group by icnu.p_k, icnu.ext_bill_nr),
    iu as
	(select icu.p_k, icu.ext_bill_nr, count(1) cnt, 
	sum((decode(d.document_type, 'IN', 1,0) * decode(icu.discount_item_flag,'N',1,0) * decode(icu.bill_section_flag_88, '9', 0,1)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icu.discount_item_flag,'N',1,0) * decode(substr(icu.bill_section_sort,'9',1),'9',0,1))) c_cnt_s, 
	sum((decode(d.document_type, 'IN', 1,0) * decode(icu.discount_item_flag,'N',1,0) * decode(icu.bill_section_flag_88, '9', 1,0)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icu.discount_item_flag,'N',1,0) * decode(substr(icu.bill_section_sort,'9',1),'9',1,0))) c_cnt_c,
	sum((decode(d.document_type, 'IN', 1,0) * decode(icu.discount_item_flag,'N',0,1) * decode(icu.bill_section_flag_88, '9', 0,1)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icu.discount_item_flag,'N',0,1) * decode(substr(icu.bill_section_sort,'9',1),'9',0,1))) d_cnt_s, 
	sum((decode(d.document_type, 'IN', 1,0) * decode(icu.discount_item_flag,'N',0,1) * decode(icu.bill_section_flag_88, '9', 1,0)) +
	    (decode(d.document_type, 'CN',1,0) * decode(icu.discount_item_flag,'N',0,1) * decode(substr(icu.bill_section_sort,'9',1),'9',1,0))) d_cnt_c,
	sum(decode(icu.transaction_type_88,'6',1,0)) d_cnt_info,
	sum((icu.item_amount_excl_vat) *
	    ((decode(icu.bill_section_flag_88, '9', 0,1) * decode(d.document_type, 'IN', 1,0)) +
	    (decode(substr(icu.bill_section_sort,'9',1), '9', 0,1) * decode(d.document_type, 'CN',1,0)))) iuat_s,
	sum((icu.item_amount_excl_vat) *
	    ((decode(icu.bill_section_flag_88, '9', 1,0) * decode(d.document_type, 'IN', 1,0)) +
	    (decode(substr(icu.bill_section_sort,'9',1), '9', 1,0) * decode(d.document_type, 'CN',1,0)))) iuat_c,
	sum((icu.item_amount_excl_vat) * (decode(icu.transaction_type_88,'6',1,0))) iuat_d_info,
	sum(icu.item_amount_excl_vat) iuat
	from init_chrg_it_u icu, docu d
--where icu.p_k = 201412270100
	where icu.p_k = d.p_k
	and icu.ext_bill_nr = d.document_number (+)
	and d.bill_format in ('MMII','MMIE')
	group by icu.p_k, icu.ext_bill_nr),
    u as
	(select p_k, document_number, count(1) cnt, 
	sum(decode(substr(usg_type,1,7),'CONTENT',1,0)) cnt_c,
	sum(decode(substr(usg_type,1,7),'CONTENT',0,1)) cnt_s,
	sum(decode(substr(usg_type,1,7),'CONTENT',bill_amount,0)) at_c,
	sum(decode(substr(usg_type,1,7),'CONTENT',0,bill_amount)) at_s,
	sum((events_total) * (decode(substr(usg_type,1,7),'CONTENT',1,0))) et_cnt_c,
	sum((events_total) * (decode(substr(usg_type,1,7),'CONTENT',0,1))) et_cnt_s,
	sum(decode(substr(usg_type,1,7),'CONTENT',gross_amount,0)) gt_c,
	sum(decode(substr(usg_type,1,7),'CONTENT',0,gross_amount)) gt_s
	from usg
--where p_k = 201412270100
	group by p_k, document_number), 
    ud as
	(select p_k, document_number, 
	sum(decode(substr(usg_type,1,7),'CONTENT',1,0)) cnt_c,
	sum(decode(substr(usg_type,1,7),'CONTENT',0,1)) cnt_s,
	sum(decode(substr(usg_type,1,7),'CONTENT',gross_amount,0)) gt_c,
	sum(decode(substr(usg_type,1,7),'CONTENT',0,gross_amount)) gt_s
	from usg_det
--where p_k = 201412270100
	group by p_k, document_number),
    r as
	(select p_k, document_number, count(1) cnt, 
	sum(decode(substr(rc_type,1,7),'CONTENT',0,1)) cnt_s,
	sum(decode(substr(rc_type,1,7),'CONTENT',1,0)) cnt_c,
	sum(decode(substr(rc_type,1,7),'CONTENT',0,bill_amount)) at_s,
	sum(decode(substr(rc_type,1,7),'CONTENT',bill_amount,0)) at_c 
	from rc
--where p_k = 201412270100
	group by p_k, document_number),
    n as
	(select p_k, document_number, count(1) cnt, 
	sum(decode(substr(nrc_type,1,7),'CONTENT',0,1)) cnt_s,
	sum(decode(substr(nrc_type,1,7),'CONTENT',1,0)) cnt_c,
	sum(decode(substr(nrc_type,1,7),'CONTENT',0,bill_amount)) at_s,
	sum(decode(substr(nrc_type,1,7),'CONTENT',bill_amount,0)) at_c 
	from nrc
--where p_k = 201412270100
	group by p_k, document_number),
    ds as
	(select p_k, document_number, count(1) cnt, 
	sum((decode(substr(disc_type,1,12),'DISC-CONTENT',0,1)) * (decode(substr(disc_type,-3,3),'-RC',1,0))) cnt_rs,
	sum((decode(substr(disc_type,1,12),'DISC-CONTENT',0,1)) * (decode(substr(disc_type,-3,3),'NRC',1,0))) cnt_ns,
	sum((decode(substr(disc_type,1,12),'DISC-CONTENT',0,1)) * (decode(substr(disc_type,-3,3),'USG',1,0))) cnt_us,
	sum((decode(substr(disc_type,1,12),'DISC-CONTENT',1,0)) * (decode(substr(disc_type,-3,3),'-RC',1,0))) cnt_rc,
	sum((decode(substr(disc_type,1,12),'DISC-CONTENT',1,0)) * (decode(substr(disc_type,-3,3),'NRC',1,0))) cnt_nc,
	sum((decode(substr(disc_type,1,12),'DISC-CONTENT',1,0)) * (decode(substr(disc_type,-3,3),'USG',1,0))) cnt_uc,
	sum(decode(substr(disc_type,1,9),'DISC-INFO',1,0)) cnt_info,
	sum(bill_amount * (decode(substr(disc_type,1,12),'DISC-CONTENT',0,1)) * (decode(substr(disc_type,-3,3),'-RC',1,0))) at_rs,
	sum(bill_amount * (decode(substr(disc_type,1,12),'DISC-CONTENT',0,1)) * (decode(substr(disc_type,-3,3),'NRC',1,0))) at_ns,
	sum(bill_amount * (decode(substr(disc_type,1,12),'DISC-CONTENT',0,1)) * (decode(substr(disc_type,-3,3),'USG',1,0))) at_us,
	sum(bill_amount * (decode(substr(disc_type,1,12),'DISC-CONTENT',1,0)) * (decode(substr(disc_type,-3,3),'-RC',1,0))) at_rc,
	sum(bill_amount * (decode(substr(disc_type,1,12),'DISC-CONTENT',1,0)) * (decode(substr(disc_type,-3,3),'NRC',1,0))) at_nc,
	sum(bill_amount * (decode(substr(disc_type,1,12),'DISC-CONTENT',1,0)) * (decode(substr(disc_type,-3,3),'USG',1,0))) at_uc,
	sum(bill_amount * (decode(substr(disc_type,1,9),'DISC-INFO',1,0))) at_info
	from disc
--where p_k = 201412270100
	group by p_k, document_number)
select
--
        dtr.p_k,
--
	dtr.document_number, dtr.document_status DOC_STATUS,
	decode(NVL(inu.c_cnt_rs,0) - NVL(r.cnt_s,0), 0,'N','Y') STD_CHG_RC_CNT_ERR,
	decode(NVL(inu.c_cnt_ns,0) - NVL(n.cnt_s,0), 0,'N','Y') STD_CHG_NRC_CNT_ERR,
	decode(NVL(inu.d_cnt_rs,0) - NVL(ds.cnt_rs,0), 0,'N','Y') STD_DIS_RC_CNT_ERR,
	decode(NVL(inu.d_cnt_ns,0) - NVL(ds.cnt_ns,0), 0,'N','Y') STD_DIS_NRC_CNT_ERR,
	decode(SIGN(NVL(r.cnt_c,0) - NVL(inu.c_cnt_rc,0)), -1,'Y','N') CON_CHG_RC_CNT_LESS,
	decode(SIGN(NVL(n.cnt_c,0) - NVL(inu.c_cnt_nc,0)), -1,'Y','N') CON_CHG_NRC_CNT_LESS,
	decode(SIGN(NVL(ds.cnt_rc,0) - NVL(inu.d_cnt_rc,0)), -1,'Y','N') STD_DIS_RC_CNT_LESS,
	decode(SIGN(NVL(ds.cnt_nc,0) - NVL(inu.d_cnt_nc,0)), -1,'Y','N') STD_DIS_NRC_CNT_LESS,
	decode(NVL(inu.inuat_rs,0) - (NVL(r.at_s,0) + NVL(ds.at_rs,0)), 0,'N','Y') STD_RC_AMT_ERR,
	decode(NVL(inu.inuat_rc,0) - (NVL(r.at_c,0) + NVL(ds.at_rc,0)), 0,'N','Y') CON_RC_AMT_ERR,
	decode(NVL(inu.inuat_ns,0) - (NVL(n.at_s,0) + NVL(ds.at_ns,0)), 0,'N','Y') STD_NRC_AMT_ERR,
	decode(NVL(inu.inuat_nc,0) - (NVL(n.at_c,0) + NVL(ds.at_nc,0)), 0,'N','Y') CON_NRC_AMT_ERR,
	decode(NVL(iu.c_cnt_s,0) - NVL(u.cnt_s,0), 0,'N','Y') STD_CHG_USG_CNT_ERR,
	decode(SIGN(NVL(u.cnt_c,0) - NVL(iu.c_cnt_c,0)), -1,'Y','N') CON_CHG_USG_CNT_LESS,
	decode(NVL(iu.d_cnt_s,0) - NVL(ds.cnt_us,0), 0,'N','Y') STD_DIS_USG_CNT_ERR,
	decode(SIGN(NVL(ds.cnt_uc,0) - NVL(iu.d_cnt_c,0)), -1,'Y','N') CON_DISC_USG_CNT_LESS,
	decode(NVL(iu.iuat_s,0) - (NVL(u.at_s,0) + NVL(ds.at_us,0)), 0,'N','Y') STD_USG_AMT_ERR,
	decode(NVL(iu.iuat_c,0) - (NVL(u.at_c,0) + NVL(ds.at_uc,0)), 0,'N','Y') CON_USG_AMT_ERR,
	decode((NVL(inu.d_cnt_info,0) + NVL(iu.d_cnt_info,0))- NVL(ds.cnt_info,0), 0,'N','Y') INF_DIS_CNT_ERR,
	decode((NVL(inu.inuat_d_info,0) + NVL(iu.iuat_d_info,0))- NVL(ds.at_info, 0), 0,'N','Y') INF_DIS_AMT_ERR,
	decode(NVL(u.et_cnt_s,0) - NVL(ud.cnt_s,0), 0,'N','Y') U_UD_STD_CNT_ERR,
	decode(NVL(u.et_cnt_c,0) - NVL(ud.cnt_c,0), 0,'N','Y') U_UD_CON_CNT_ERR,
	decode(NVL(u.gt_s,0) - NVL(ud.gt_s,0), 0,'N','Y') U_UD_STD_AMT_ERR,
	decode(NVL(u.gt_c,0) - NVL(ud.gt_c,0), 0,'N','Y') U_UD_CON_AMT_ERR,
	NVL(inu.c_cnt_rs, 0) INU_STD_CHG_RC_CNT, NVL(r.cnt_s, 0) STD_RC_CNT,
	NVL(inu.c_cnt_ns, 0) INU_STD_CHG_NRC_CNT, NVL(n.cnt_s, 0) STD_NRC_CNT,
	NVL(inu.d_cnt_rs, 0) INU_STD_DIS_RC_CNT, NVL(ds.cnt_rs, 0) STD_DIS_RC_CNT,
	NVL(inu.d_cnt_ns, 0) INU_STD_DIS_NRC_CNT, NVL(ds.cnt_ns, 0) STD_DIS_NRC_CNT,
	NVL(inu.c_cnt_rc, 0) INU_CON_CHG_RC_CNT, NVL(r.cnt_c, 0) CON_RC_CNT,
	NVL(inu.c_cnt_nc, 0) INU_CON_CHG_NRC_CNT, NVL(n.cnt_c, 0) CON_NRC_CNT,
	NVL(inu.d_cnt_rc, 0) INU_CON_DIS_RC_CNT, NVL(ds.cnt_rc, 0) CON_DIS_RC_CNT,
	NVL(inu.d_cnt_nc, 0) INU_CON_DIS_NRC_CNT, NVL(ds.cnt_nc, 0) CON_DIS_NRC_CNT,
	NVL(inu.inuat_rs, 0) INU_STD_RC_TOT_AMT, NVL(r.at_s,0) STD_RC_AMT, NVL(ds.at_rs,0) STD_RC_DIS_AMT,
	NVL(inu.inuat_rc, 0) INU_CON_RC_TOT_AMT, NVL(r.at_c,0) CON_RC_AMT, NVL(ds.at_rc,0) CON_RC_DIS_AMT,
	NVL(inu.inuat_ns, 0) INU_STD_NRC_TOT_AMT, NVL(n.at_s,0) STD_NRC_AMT,  NVL(ds.at_ns,0) STD_NRC_DIS_AMT,
	NVL(inu.inuat_nc, 0) INU_CON_NRC_TOT_AMT, NVL(n.at_c,0) CON_NRC_AMT, NVL(ds.at_nc,0) CON_NRC_DIS_AMT,
	NVL(iu.c_cnt_s, 0) IU_STD_CHG_CNT, NVL(u.cnt_s, 0) STD_USG_CNT,
	NVL(iu.c_cnt_c, 0) IU_CON_CHG_CNT, NVL(u.cnt_c, 0) CON_USG_CNT, 
	NVL(iu.d_cnt_s,0) IU_STD_DIS_CNT, NVL(ds.cnt_us,0) STD_DIS_USG_CNT,
	NVL(iu.d_cnt_c,0) IU_CON_DIS_CNT, NVL(ds.cnt_uc,0) CON_DIS_USG_CNT,
	NVL(iu.iuat_s, 0) IU_STD_USG_AMT, NVL(u.at_s,0) STD_USG_AMT, NVL(ds.at_us,0) STD_USG_DIS_AMT,
	NVL(iu.iuat_c, 0) IU_CON_USG_AMT, NVL(u.at_c,0) CON_USG_AMT, NVL(ds.at_uc,0) CON_USG_DIS_AMT,
	NVL(inu.d_cnt_info,0) INU_INF_DIS_CNT, NVL(iu.d_cnt_info,0) IU_INF_DIS_CNT, NVL(ds.cnt_info,0) INF_DIS_CNT,
	NVL(inu.inuat_d_info,0) INU_INF_DIS_AMT, NVL(iu.iuat_d_info,0) IU_INF_DIS_AMT, NVL(ds.at_info,0) INF_DIS_AMT,
	NVL(u.et_cnt_s,0) STD_USG_EV_TOT, NVL(ud.cnt_s,0) STD_USGD_CNT,
	NVL(u.et_cnt_c,0) CON_USG_EV_TOT, NVL(ud.cnt_c,0) CON_USGD_CNT,
	NVL(u.gt_s,0) STD_USG_GAMT, NVL(ud.gt_s,0) STD_USGD_GAMT,
	NVL(u.gt_c,0) CON_USG_GAMT, NVL(ud.gt_c,0) CON_USGD_GAMT
from inu, iu, r, n, u, ud, ds, docu_track dtr
--
--where dtr.p_k = 201412270100
--
        where dtr.p_k = iu.p_k (+)
        and dtr.p_k = inu.p_k (+)
        and dtr.p_k = r.p_k (+)
        and dtr.p_k = n.p_k (+)
        and dtr.p_k = ud.p_k (+)
        and dtr.p_k = ds.p_k (+)
--
	and dtr.document_number = inu.ext_bill_nr (+)
	and dtr.document_number = iu.ext_bill_nr (+)
	and dtr.document_number = r.document_number (+)
	and dtr.document_number = n.document_number (+)
	and dtr.document_number = u.document_number (+)
	and dtr.document_number = ud.document_number (+)
	and dtr.document_number = ds.document_number (+)
	and (NVL(inu.c_cnt_rs,0) <> NVL(r.cnt_s,0)
	or NVL(inu.c_cnt_ns,0) <> NVL(n.cnt_s,0)
	or NVL(inu.c_cnt_rc,0) > NVL(r.cnt_c,0)
	or NVL(inu.c_cnt_nc,0) > NVL(n.cnt_c,0)
	or NVL(inu.d_cnt_rs,0) <> NVL(ds.cnt_rs,0)
	or NVL(inu.d_cnt_ns,0) <> NVL(ds.cnt_ns,0)
	or NVL(inu.d_cnt_rc,0) > NVL(ds.cnt_rc,0)
	or NVL(inu.d_cnt_nc,0) > NVL(ds.cnt_nc,0)
	or NVL(inu.inuat_rs,0) <> (NVL(r.at_s,0) + NVL(ds.at_rs,0))
	or NVL(inu.inuat_rc,0) <> (NVL(r.at_c,0) + NVL(ds.at_rc,0))
	or NVL(inu.inuat_ns,0) <> (NVL(n.at_s,0) + NVL(ds.at_ns,0))
	or NVL(inu.inuat_nc,0) <> (NVL(n.at_c,0) + NVL(ds.at_nc,0))
	or NVL(iu.c_cnt_s,0) <> NVL(u.cnt_s,0)
	or NVL(iu.d_cnt_s,0) <> NVL(ds.cnt_us,0)
	or NVL(iu.c_cnt_c,0) > NVL(u.cnt_c,0)
	or NVL(iu.d_cnt_c,0) > NVL(ds.cnt_uc,0)
	or NVL(iu.iuat_s,0) <> (NVL(u.at_s,0) + NVL(ds.at_us,0))
	or NVL(iu.iuat_c,0) <> (NVL(u.at_c,0) + NVL(ds.at_uc,0))
	or NVL(inu.d_cnt_info,0) + NVL(iu.d_cnt_info,0) <> NVL(ds.cnt_info,0)
	or NVL(inu.inuat_d_info,0) + NVL(iu.iuat_d_info,0) <> NVL(ds.at_info,0)
	or NVL(u.et_cnt_s,0) <> NVL(ud.cnt_s,0)
	or NVL(u.et_cnt_c,0) <> NVL(ud.cnt_c,0)
	or NVL(u.gt_s,0) <> NVL(ud.gt_s,0)
	or NVL(u.gt_c,0) <> NVL(ud.gt_c,0));

select *
from your_view_name_here
where P_K = 201412270100
/

Kevin

[Updated on: Wed, 07 January 2015 22:18]

Report message to a moderator

Re: creation of view by passing the values dynamically [message #631029 is a reply to message #631013]

Thu, 08 January 2015 00:58

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Thank you very much to all

Report message to a moderator

Previous Topic:	How to find tables with nullable Unique Keys
Next Topic:	Need to get only decimal values upto 2 digits if we have nothing in decimal points that has to be 0

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 00:58:46 CDT 2024