Вопрос: Как вытащить данные по нескольким условиям
Добрый день! У меня такая проблема мне нужна вытащить данные с несколькими условиями с таблицы. Вот условия
de.SERVICE_CLASS in( 'T','d','A') по этому условию должен выходить одно значение но у меня оно выходить несколько значение как можно сделать чтобы выходило одно значение. Как показано на картинке
select v.datet,
v.фИЛИАЛ,
v.nam Имя,
v.contr_name as Контракт_имя,
v.ОБОРОТЫ ОБОРОТЫ,
abs(v.ДОХОДЫ) ДОХОДЫ,
b.s Возмещение,
v.np,
b.nv,
b.db,
v.ds,
sum(abs(v.rev)) Реверс,
sum(((-1) * v.revd)) Реверс_доход,
sum(v.ОБОРОТЫ + v.ДОХОДЫ - b.s + v.rev - v.revd+ v.revd + v.revd) as Итог,
v.scheme_nam
from (select de.LOCAL_DATE datet,
sg.name as фИЛИАЛ,
ac.contract_name nam,
ac.contract_number contr_name,
case when sum(de.settl_AMOUNT)<0 then 0 else sum(de.settl_AMOUNT) end ОБОРОТЫ,
sum((de.FEE_AMOUNT)) as ДОХОДЫ,
sch.scheme_name scheme_nam,
de.SERVICE_CLASS ds,
nvl(p.rq, 0) rev,
ac.id np,
nvl(p.rd, 0) revd
from ows.acnt_contract ac
join ows.acc_scheme sch
on ac.acc_scheme__id = sch.id
join ows.service_group sg
on sg.code = ac.service_group
and sg.amnd_state = 'A'
and sg.pcat = 'C'
and sg.ccat = 'P'
join OWS.DOC_ENTRY de
on de.LOCAL_DATE >= to_date('05.11.2018', 'dd.mm.yyyy')
and de.LOCAL_DATE <= to_date('05.11.2018', 'dd.mm.yyyy')
and de.SERVICE_CLASS in( 'T','d','A') AND DE.REQUEST_CAT in ('P', 'J')
join OWS.ACCOUNT A
on a.id = de.ACCOUNT__OID
and a.Account_Type = '3'
and a.code = 'X'
AND AC.ID = a.ACNT_CONTRACT__OID
left join (select DECODE(Des.REQUEST_CAT,
'R',
(sum(des.SETTL_AMOUNT)),
'P',
0) AS RQ,
DECODE(Des.REQUEST_CAT,
'R',
(sum(des.fee_AMOUNT)),
'P',
0) AS RD,
acc.id r
from ows.doc_entry des
join ows.acnt_contract acc
on acc.client_type = 168
and acc.ccat = 'C'
and acc.amnd_state = 'A'
and acc.pcat = 'M'
and acc.contract_number in ('001-COMM912905', '001-COMM793350','001-COMM201710','001-COMM740200')
-- and substr(acc.contract_number,1,8)='001-COMM'
and acc.CONTRACT_NAME not like ('CNP%')
and acc.CONTRACT_NAME not like ('%RPC%')
join OWS.ACCOUNT Av
on av.id = des.ACCOUNT__OID
and av.Account_Type = '3'
and av.code = 'X'
AND Acc.ID = av.ACNT_CONTRACT__OID
where des.LOCAL_DATE >=
to_date('05.11.2018', 'dd.mm.yyyy')
and des.LOCAL_DATE <=
to_date('05.11.2018', 'dd.mm.yyyy')
and des.SERVICE_CLASS in( 'T','d','A') AND DEs.REQUEST_CAT = 'R'
group by acc.id, Des.REQUEST_CAT) p
on p.r = ac.id
where ac.client_type = 168
and ac.ccat = 'C'
and ac.amnd_state = 'A'
and ac.pcat = 'M'
and ac.contract_number in ('001-COMM912905', '001-COMM793350','001-COMM201710','001-COMM740200')
-- and substr(ac.contract_number,1,8)='001-COMM'
and ac.Contract_Name not like ('CNP%')
and ac.CONTRACT_NAME not like ('%RPC%')
group by de.LOCAL_DATE,
sg.name,
ac.contract_name,
ac.contract_number,
ac.id,
p.rq,
de.SERVICE_CLASS,
sch.scheme_name,
p.rd) v,
(
select
--DECODE(des.SERVICE_CLASS, 'T', (sum(des.SETTL_AMOUNT)),'d',0,'A',(sum(des.SETTL_AMOUNT)) ) AS s,
-- sum((des.SETTL_AMOUNT)) s,
case when des.SERVICE_CLASS='T' then (sum(des.SETTL_AMOUNT))
when des.SERVICE_CLASS='d' then 0
when des.SERVICE_CLASS='A' then 0
end s,
des.SERVICE_CLASS db,
ac.id nv
from ows.acnt_contract ac
join OWS.DOC_ENTRY des
on des.LOCAL_DATE >= to_date('05.11.2018', 'dd.mm.yyyy')
and des.LOCAL_DATE <= to_date('05.11.2018', 'dd.mm.yyyy')
and des.SERVICE_CLASS in( 'T','d','A')
AND DEs.REQUEST_CAT in ('P')
join OWS.ACCOUNT A
on a.id = des.ACCOUNT__OID
and a.Account_Type in ('4')
and a.code in ('B')
AND AC.ID = a.ACNT_CONTRACT__OID
where ac.client_type = 168
and ac.ccat = 'C'
and ac.amnd_state = 'A'
and ac.pcat = 'M' --
and ac.contract_number in ('001-COMM912905', '001-COMM793350','001-COMM201710','001-COMM740200')
-- and substr(ac.contract_number,1,8)='001-COMM'
and ac.CONTRACT_NAME not like ('CNP%')
and ac.CONTRACT_NAME not like ('%RPC%')
group by des.LOCAL_DATE, ac.id,des.SERVICE_CLASS
) b
where b.nv = v.np
group by v.datet,
v.фИЛИАЛ,
v.nam,
v.contr_name,
v.ОБОРОТЫ,
v.ДОХОДЫ,
v.np,
b.nv,
v.ds,
v.scheme_nam,
b.s,
v.rev,
b.db,
v.revd
order by v.contr_name
de.SERVICE_CLASS in( 'T','d','A')
К сообщению приложен файл. Размер - 136Kb
Удобный поиск информации с популярных форумов в одном месте