select f_get_dq_nm(b.agent_id) dq_nm,
a.contract_id,
a.contract_nm,
a.contract_status,
(to_char(a.start_dt, 'yyyy-mm-dd') || '~' ||
to_char(a.end_dt, 'yyyy-mm-dd')) contract_date,
b.agent_nm,
a.contract_sta,
f_get_hmg_prod_nm_contract(a.contract_id) prod_nm,
a.addr,
c.rebate_type,
c.qty_1,
c.qty_2,
c.qty_3,
c.qty_4,
d.rebate_type,
d.qty_1,
d.qty_2,
d.qty_3,
d.qty_4,
e.contract_id,
e.mon,
e.product_id,
e.qty,
f.type_nm
from ces_hmg_contract a,
ces_hmg_agent b,
ces_hmg_contract_rebate c,
ces_hmg_contract_rebate_sup d,
ces_hmg_contract_goal e,
hmg.cm1039 f
where 1 = 1
and a.agent_id = b.agent_id
and a.contract_id = c.contract_id
and a.contract_id = d.contract_id
and a.contract_id = e.contract_id
and e.product_id = f.type_id
and c.rebate_type='0'
and b.hmg_status = '1'
and c.contract_id = 'F100140'
and d.rebate_type = 'Y'
and e.mon = '2014-12'
union all
select f_get_dq_nm(b.agent_id) dq_nm,
a.contract_id,
a.contract_nm,
a.contract_status,
(to_char(a.start_dt, 'yyyy-mm-dd') || '~' ||
to_char(a.end_dt, 'yyyy-mm-dd')) contract_date,
b.agent_nm,
a.contract_sta,
f_get_hmg_prod_nm_contract(a.contract_id) prod_nm,
a.addr,
c.rebate_type,
c.qty_1,
c.qty_2,
c.qty_3,
c.qty_4,
d.rebate_type,
d.qty_1,
d.qty_2,
d.qty_3,
d.qty_4,
e.contract_id,
e.mon,
e.product_id,
e.qty,
f.type_nm
from ces_hmg_contract a,
ces_hmg_agent b,
ces_hmg_contract_rebate c,
ces_hmg_contract_rebate_sup d,
ces_hmg_contract_goal e,
hmg.cm1039 f
where 1 = 1
and a.agent_id = b.agent_id
and a.contract_id = c.contract_id
and a.contract_id = d.contract_id
and a.contract_id = e.contract_id
and e.product_id = f.type_id
and c.rebate_type='0'
and b.hmg_status = '1'
and c.contract_id = 'F100140'
and d.rebate_type = 'M'
and e.mon = '2014-12'
a.contract_id,
a.contract_nm,
a.contract_status,
(to_char(a.start_dt, 'yyyy-mm-dd') || '~' ||
to_char(a.end_dt, 'yyyy-mm-dd')) contract_date,
b.agent_nm,
a.contract_sta,
f_get_hmg_prod_nm_contract(a.contract_id) prod_nm,
a.addr,
c.rebate_type,
c.qty_1,
c.qty_2,
c.qty_3,
c.qty_4,
d.rebate_type,
d.qty_1,
d.qty_2,
d.qty_3,
d.qty_4,
e.contract_id,
e.mon,
e.product_id,
e.qty,
f.type_nm
from ces_hmg_contract a,
ces_hmg_agent b,
ces_hmg_contract_rebate c,
ces_hmg_contract_rebate_sup d,
ces_hmg_contract_goal e,
hmg.cm1039 f
where 1 = 1
and a.agent_id = b.agent_id
and a.contract_id = c.contract_id
and a.contract_id = d.contract_id
and a.contract_id = e.contract_id
and e.product_id = f.type_id
and c.rebate_type='0'
and b.hmg_status = '1'
and c.contract_id = 'F100140'
and d.rebate_type = 'Y'
and e.mon = '2014-12'
union all
select f_get_dq_nm(b.agent_id) dq_nm,
a.contract_id,
a.contract_nm,
a.contract_status,
(to_char(a.start_dt, 'yyyy-mm-dd') || '~' ||
to_char(a.end_dt, 'yyyy-mm-dd')) contract_date,
b.agent_nm,
a.contract_sta,
f_get_hmg_prod_nm_contract(a.contract_id) prod_nm,
a.addr,
c.rebate_type,
c.qty_1,
c.qty_2,
c.qty_3,
c.qty_4,
d.rebate_type,
d.qty_1,
d.qty_2,
d.qty_3,
d.qty_4,
e.contract_id,
e.mon,
e.product_id,
e.qty,
f.type_nm
from ces_hmg_contract a,
ces_hmg_agent b,
ces_hmg_contract_rebate c,
ces_hmg_contract_rebate_sup d,
ces_hmg_contract_goal e,
hmg.cm1039 f
where 1 = 1
and a.agent_id = b.agent_id
and a.contract_id = c.contract_id
and a.contract_id = d.contract_id
and a.contract_id = e.contract_id
and e.product_id = f.type_id
and c.rebate_type='0'
and b.hmg_status = '1'
and c.contract_id = 'F100140'
and d.rebate_type = 'M'
and e.mon = '2014-12'
select
distinct f_get_dq_nm(b.agent_id) dq_nm,
a.contract_id,
a.contract_nm,
a.contract_status,
(to_char(a.start_dt, 'yyyy-mm-dd') || '~' ||
to_char(a.end_dt, 'yyyy-mm-dd')) contract_date,
b.agent_nm,
a.contract_sta,
f_get_hmg_prod_nm_contract(a.contract_id) prod_nm,
a.addr,
c.rebate_type,
c.qty_1,
c.qty_2,
c.qty_3,
c.qty_4,
DECODE(d.rebate_type,'Y','Y','Y') c1, DECODE(d.rebate_type,'M','M','M') c2,
d.qty_1,
d.qty_2,
d.qty_3,
d.qty_4,
e.contract_id,
e.mon,
e.product_id,
e.qty,
f.type_nm
from ces_hmg_contract a,
ces_hmg_agent b,
ces_hmg_contract_rebate c,
ces_hmg_contract_rebate_sup d,
ces_hmg_contract_goal e,
hmg.cm1039 f
where 1 = 1
and a.agent_id = b.agent_id
and a.contract_id = c.contract_id
and a.contract_id = d.contract_id
and a.contract_id = e.contract_id
and e.product_id = f.type_id
and c.rebate_type='0'
and b.hmg_status = '1'
and c.contract_id = 'F100140'
--and d.rebate_type = 'Y'
--and e.mon = '2014-12'
我也是新手,不知道有没有帮助。